postgresql_idx only takes into account idxname (not schema)

[rlaager]

SUMMARY

postgresql_idx only takes into account the idxname, not the schema, when deciding whether the index already exists.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_idx

ANSIBLE VERSION```paste below

ansible [core 2.13.13]
config file = /etc/ansible/ansible.cfg
configured module search path = ['/srv/ansible/modules']
ansible python module location = /srv/ansible/.venv/lib/python3.8/site-packages/ansible
ansible collection location = /srv/ansible/.venv/share/ansible/collections:/home/rlaager/.ansible/collections:/usr/share/ansible/collections:/usr/local/share/ansible/collections
executable location = /srv/ansible/.venv/bin/ansible
python version = 3.8.10 (default, Nov 22 2023, 10:22:35) [GCC 9.4.0]
jinja version = 3.1.3
libyaml = False

##### COLLECTION VERSION
<!--- Paste verbatim output from "ansible-galaxy collection list <namespace>.<collection>"  between the quotes
for example: ansible-galaxy collection list community.general
-->
```paste below
Collection           Version
-------------------- -------
community.postgresql 3.4.0

CONFIGURATION

ANSIBLE_NOCOWS(/etc/ansible/ansible.cfg) = True
CACHE_PLUGIN(/etc/ansible/ansible.cfg) = jsonfile
CACHE_PLUGIN_CONNECTION(/etc/ansible/ansible.cfg) = /var/cache/ansible/facts
CACHE_PLUGIN_TIMEOUT(/etc/ansible/ansible.cfg) = 129600
COLLECTIONS_PATHS(/etc/ansible/ansible.cfg) = ['/srv/ansible/.venv/share/ansible/collections', '/home/rlaager/.ansible/collections', '/usr/share/ansible/collections', '/usr/local/share/ansible/collections']
DEFAULT_ACTION_PLUGIN_PATH(/etc/ansible/ansible.cfg) = ['/usr/share/ansible/plugins/action', '/srv/ansible/plugins/action']
DEFAULT_BECOME(/etc/ansible/ansible.cfg) = True
DEFAULT_BECOME_USER(/etc/ansible/ansible.cfg) = root
DEFAULT_CALLBACK_PLUGIN_PATH(/etc/ansible/ansible.cfg) = ['/usr/share/ansible/plugins/callback', '/srv/ansible/plugins/callback']
DEFAULT_FORCE_HANDLERS(/etc/ansible/ansible.cfg) = True
DEFAULT_GATHERING(/etc/ansible/ansible.cfg) = smart
DEFAULT_HOST_LIST(/etc/ansible/ansible.cfg) = ['/srv/ansible/inventories']
DEFAULT_LOG_PATH(/etc/ansible/ansible.cfg) = /var/log/ansible/ansible.log
DEFAULT_MODULE_PATH(/etc/ansible/ansible.cfg) = ['/srv/ansible/modules']
DEFAULT_REMOTE_USER(/etc/ansible/ansible.cfg) = ansible
DEFAULT_ROLES_PATH(/etc/ansible/ansible.cfg) = ['/srv/ansible/roles']
DEFAULT_STDOUT_CALLBACK(/etc/ansible/ansible.cfg) = wiktel
DEFAULT_STRATEGY(/etc/ansible/ansible.cfg) = host_pinned
DEFAULT_VAULT_PASSWORD_FILE(/etc/ansible/ansible.cfg) = /etc/ansible/vault_password
DIFF_ALWAYS(/etc/ansible/ansible.cfg) = True
DISPLAY_SKIPPED_HOSTS(/etc/ansible/ansible.cfg) = False
INJECT_FACTS_AS_VARS(/etc/ansible/ansible.cfg) = False
INVENTORY_ENABLED(/etc/ansible/ansible.cfg) = ['yaml']
PARAMIKO_HOST_KEY_AUTO_ADD(/etc/ansible/ansible.cfg) = True

OS / ENVIRONMENT

Ubuntu 20.04 with Ansible installed in virtualenv

STEPS TO REPRODUCE

- name: create freeswitch database schemas
  become: yes
  become_user: postgres
  community.postgresql.postgresql_schema:
    db: freeswitch
    name: "freeswitch_{{ item|replace('-', '_') }}"
    owner: "freeswitch_{{ item|replace('-', '_') }}"
  loop: "{{ pbx_instances.keys() }}"
  loop_control:
    label: "freeswitch_{{ item|replace('-', '_') }}"
  # This will fail if PostgreSQL has not been installed, etc.
  ignore_errors: "{{ ansible_check_mode }}"
  tags:
    - freeswitch

- name: create CDR table
  become: yes
  become_user: postgres
  community.postgresql.postgresql_table:
    db: freeswitch
    name: >-
      freeswitch_{{ item|replace('-', '_') }}.cdr
    columns:
      - id                        serial PRIMARY KEY
      - caller_id_name            varchar
      - caller_id_number          varchar
      - destination_number        varchar NOT NULL
      - context                   varchar NOT NULL
      - start_stamp               timestamp with time zone NOT NULL
      - answer_stamp              timestamp with time zone
      - end_stamp                 timestamp with time zone NOT NULL
      - duration                  int NOT NULL
      - billsec                   int NOT NULL
      - hangup_cause              varchar NOT NULL
      - uuid                      uuid NOT NULL
      - bleg_uuid                 uuid
      - accountcode               varchar
      - read_codec                varchar
      - write_codec               varchar
      - sip_hangup_disposition    varchar
      - ani                       varchar
    owner: "freeswitch_{{ item|replace('-', '_') }}"
  # This will fail if PostgreSQL has not been installed, etc.
  ignore_errors: "{{ ansible_check_mode }}"
  loop: "{{ pbx_instances.keys() }}"
  loop_control:
    label: "freeswitch_{{ item|replace('-', '_') }}"
  tags:
    - freeswitch

- name: create CDR start_stamp index
  become: yes
  become_user: postgres
  community.postgresql.postgresql_idx:
    db: freeswitch
    schema: >-
      freeswitch_{{ item|replace('-', '_') }}
    table: cdr
    columns: start_stamp
    idxname: cdr_start_stamp_key
  # This will fail if PostgreSQL has not been installed, etc.
  ignore_errors: "{{ ansible_check_mode }}"
  loop: "{{ pbx_instances.keys() }}"
  loop_control:
    label: "freeswitch_{{ item|replace('-', '_') }}"
  tags:
    - freeswitch

EXPECTED RESULTS

On the first run, I would expect it to create one index per schema.

ACTUAL RESULTS

On the first run, it creates the index for the first schema and then the subsequent iterations of the loop are "ok".

TASK [pbx : create CDR start_stamp index] *****************************************************************************************
changed: [server] => (item=freeswitch_instance1)
ok: [server] => (item=freeswitch_instance2)
ok: [server] => (item=freeswitch_instance3)