postgresql_db fails with Postgres 17rc1

[cameronmurdoch]

SUMMARY

Due to pg_catalog changes in the forthcoming pg17 release, postgresql_db throws an exception on all runs other than the first.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_db

ANSIBLE VERSION

ansible [core 2.16.10]
  config file = /home/cameronm/.ansible.cfg
  configured module search path = ['/home/cameronm/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.12/site-packages/ansible
  ansible collection location = /home/cameronm/ansible_dev/ansible_collections
  executable location = /usr/bin/ansible
  python version = 3.12.5 (main, Aug 23 2024, 00:00:00) [GCC 14.2.1 20240801 (Red Hat 14.2.1-1)] (/usr/bin/python3)
  jinja version = 3.1.4
  libyaml = True

COLLECTION VERSION

# /home/cameronm/ansible_dev/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.5.0 

CONFIGURATION

COLLECTIONS_PATHS(/home/cameronm/.ansible.cfg) = ['/home/cameronm/ansible_dev/ansible_collections']
CONFIG_FILE() = /home/cameronm/.ansible.cfg
DEFAULT_BECOME_METHOD(/home/cameronm/.ansible.cfg) = su
DEFAULT_STDOUT_CALLBACK(/home/cameronm/.ansible.cfg) = debug
EDITOR(env: EDITOR) = /usr/bin/vim

OS / ENVIRONMENT

Fedora 40
Nothing really changed from standard

STEPS TO REPRODUCE

Creating a database with postgresql_db module works as expected, but on all subsequent runs of the play we hit an exception.

- community.postgresql.postgresql_db:
    name: testdb

EXPECTED RESULTS

That the playbook completes with no changes

ACTUAL RESULTS

TASK [community.postgresql.postgresql_db] ********************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: HINT:  Perhaps you meant to reference the column "pg_database.datlocale".
fatal: [localhost]: FAILED! => {
    "changed": false
}

MSG:

Database query failed: column "daticulocale" does not exist
LINE 4: ...  datcollate AS lc_collate, datctype AS lc_ctype, daticuloca...
                                                             ^
HINT:  Perhaps you meant to reference the column "pg_database.datlocale".

This is because the pg_database column daticulocale has been renamed to datlocale, I think as part of the work to add a builtin collation provider and get_db_info() chokes on this.

I would be interested in helping to fix this and can hopefully submit a PR quite soon.

[Andersson007]

@cameronmurdoch hello, thanks for reporting the issue! Sure, go ahead, thanks!
A couple of (probably obvious) things worth mentioning:

• The backwards compatibility for older versions should be preserved.
• There's a short quick start dev guide that can help
• All please add a changelog fragment to the PR
• Any questions/concerns/circumstances change - please let us know so that someone else to pick it up

thanks!

[cameronmurdoch]

Thanks @Andersson007

I have some working code, but there are two issues here.

1. postgresql_db doesn't really work at all with pg17 due to the changes to pg_database.

2. We should probably add support for the new builtin locale provider, but that is a bit more work.

I started on 1. but ended up sliding into doing 2. :-)

So I think I will initially submit a PR to just fix 1. which looks to be a fairly trivial change. 2. should be a separate issue I think.

[Andersson007]

Thanks @Andersson007

I have some working code, but there are two issues here.

1. postgresql_db doesn't really work at all with pg17 due to the changes to pg_database.
2. We should probably add support for the new builtin locale provider, but that is a bit more work.

I started on 1. but ended up sliding into doing 2. :-)

So I think I will initially submit a PR to just fix 1. which looks to be a fairly trivial change. 2. should be a separate issue I think.

@cameronmurdoch sounds like a plan, thanks