PostgreSQL info module fails with Cannot execute SQL syntax error at or near "b'oid'"

[chriscroome]

SUMMARY

On a new Debian 12 server the info module is failing for me with:

Cannot execute SQL 'SELECT s.b'oid', s.b'subdbid', s.b'subskiplsn', s.b'subname', s.b'subowner', s.b'subenabled', s.b'subbinary', s.b'substream', s.b'subtwophasestate', s.b'subdisableonerr', s.b'subconninfo', s.b'subslotname', s.b'subsynccommit', s.b'subpublications', r.rolname AS ownername, d.datname AS dbname FROM pg_catalog.pg_subscription s JOIN pg_catalog.pg_database d ON s.subdbid = d.oid JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid': syntax error at or near "b'oid'"
LINE 1: SELECT s.b'oid', s.b'subdbid', s.b'subskiplsn', s.b'subname'...

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_info module

ANSIBLE VERSION

ansible [core 2.17.7]
  config file = /home/chris/animorph-coop/servers/ansible.cfg
  configured module search path = ['/home/chris/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/chris/.local/pipx/venvs/ansible/lib/python3.11/site-packages/ansible
  ansible collection location = /home/chris/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/chris/.local/bin/ansible
  python version = 3.11.2 (main, Nov 30 2024, 21:22:50) [GCC 12.2.0] (/home/chris/.local/pipx/venvs/ansible/bin/python)
  jinja version = 3.1.5
  libyaml = True

COLLECTION VERSION

# /home/chris/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.9.1  

# /home/chris/.local/pipx/venvs/ansible/lib/python3.11/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.9.0  

CONFIGURATION

ANSIBLE_FORCE_COLOR(/home/chris/animorph-coop/servers/ansible.cfg) = True
ANSIBLE_NOCOWS(/home/chris/animorph-coop/servers/ansible.cfg) = True
ANSIBLE_PIPELINING(/home/chris/animorph-coop/servers/ansible.cfg) = True
COLOR_DEBUG(env: ANSIBLE_COLOR_DEBUG) = blue
CONFIG_FILE() = /home/chris/animorph-coop/servers/ansible.cfg
DEFAULT_HOST_LIST(/home/chris/animorph-coop/servers/ansible.cfg) = ['/home/chris/animorph-coop/servers/hosts.yml']
DEFAULT_ROLES_PATH(/home/chris/animorph-coop/servers/ansible.cfg) = ['/home/chris/animorph-coop/servers/galaxy/roles']
DEFAULT_TIMEOUT(/home/chris/animorph-coop/servers/ansible.cfg) = 60
DEFAULT_VAULT_PASSWORD_FILE(/home/chris/animorph-coop/servers/ansible.cfg) = /home/chris/animorph-coop/servers/.vault.passwd
DISPLAY_SKIPPED_HOSTS(/home/chris/animorph-coop/servers/ansible.cfg) = False
DUPLICATE_YAML_DICT_KEY(/home/chris/animorph-coop/servers/ansible.cfg) = error
EDITOR(env: EDITOR) = /usr/bin/vim
RETRY_FILES_ENABLED(/home/chris/animorph-coop/servers/ansible.cfg) = False

OS / ENVIRONMENT

Debian 12.9

apt info postgresql
Package: postgresql
Version: 15+248
Priority: optional
Section: database
Source: postgresql-common (248)
Maintainer: Debian PostgreSQL Maintainers <[email protected]>
Installed-Size: 15.4 kB
Depends: postgresql-15
Suggests: postgresql-doc
Tag: devel::lang:sql, interface::daemon, network::server, network::service,
 role::metapackage, role::program, suite::postgresql, works-with::db
Download-Size: 10.1 kB
APT-Manual-Installed: yes
APT-Sources: https://deb.debian.org/debian bookworm/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

STEPS TO REPRODUCE

I'm using this role with the following config:

postgresql: true
postgresql_db: plane
postgresql_db_owner: plane
postgresql_db_state: present
postgresql_user: plane

This is the task in question.

EXPECTED RESULTS

Not a failure, the thing I don't understand is that the tests I have for this role that are run via Molecule / GitLab CI don't fail.

ACTUAL RESULTS

TASK [postgresql : PostgreSQL info] ************************************************************************************************************************************************
fatal: [plane.webarch.coop]: FAILED! => 
    changed: false
    msg: |-
        Cannot execute SQL 'SELECT s.b'oid', s.b'subdbid', s.b'subskiplsn', s.b'subname', s.b'subowner', s.b'subenabled', s.b'subbinary', s.b'substream', s.b'subtwophasestate', s.b'subdisableonerr', s.b'subconninfo', s.b'subslotname', s.b'subsynccommit', s.b'subpublications', r.rolname AS ownername, d.datname AS dbname FROM pg_catalog.pg_subscription s JOIN pg_catalog.pg_database d ON s.subdbid = d.oid JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid': syntax error at or near "b'oid'"
        LINE 1: SELECT s.b'oid', s.b'subdbid', s.b'subskiplsn', s.b'subname'...
                         ^

[betanummeric]

Hi @chriscroome , which version of psycopg are you using? psycopg2 or psycopg (version 3)?

[chriscroome]

Thanks @betanummeric the server had both versions:

aptitude search psycopg
p   python-psycopg-doc                                                                                                              - PostgreSQL database adapter for Python 3 (documentation package)                                                                          
p   python-psycopg2-doc                                                                                                             - Python module for PostgreSQL (documentation package)                                                                                      
i   python3-psycopg                                                                                                                 - PostgreSQL database adapter for Python 3                                                                                                  
p   python3-psycopg-pool                                                                                                            - PostgreSQL database adapter for Python 3: connection pool                                                                                 
i   python3-psycopg2                                                                                                                - Python 3 module for PostgreSQL                                                                                                            
p   python3-psycopg2cffi                                                                                                            - implementation of the psycopg2 module using cffi                                                                                          
v   python3-types-psycopg2                                                                                                          -                                                                                                                                           

Doing apt purge python3-psycopg solved it, thanks!

[Andersson007]

@chriscroome if you could test the following solutions manually by finding the module in your local setup, backing it up, editing the same as in the following PRs and test (one by one), it'd be great:

• [DRAFT] postgresql_info: fix failure when col names are returned as binary #804
• [DRAFT] postgresql_info: handle encoding #805

[Andersson007]

We need manual testing for ^. If no feedback / opportunity to test it in near future, we should close the PRs to keep the tracker clean

[chriscroome]

I have installed both modules on a development server:

aptitude search python3-psycopg
i  python3-psycopg - PostgreSQL database adapter for Python 3
p  python3-psycopg-pool - PostgreSQL database adapter for Python 3: connection pool
i  python3-psycopg2 - Python 3 module for PostgreSQL
p  python3-psycopg2cffi - implementation of the psycopg2 module using cffi

And have these versions of the collection locally:

ansible-galaxy collection list | grep -i postgresql
community.postgresql                     3.10.2 
community.postgresql                     3.9.0  

After removing python3-psycopg2 from the server

aptitude search python3-psycopg
i   python3-psycopg - PostgreSQL database adapter for Python 3
p   python3-psycopg-pool - PostgreSQL database adapter for Python 3: connection pool                                                                                 
p   python3-psycopg2 - Python 3 module for PostgreSQL                                                                                                            
p   python3-psycopg2cffi - implementation of the psycopg2 module using cffi   

The community.postgresql.postgresql_info module runs without an error.

After installing python3-psycopg2 on the server the community.postgresql.postgresql_info module still runs fine:

aptitude search python3-psycopg
p  python3-psycopg - PostgreSQL database adapter for Python 3
p  python3-psycopg-pool - PostgreSQL database adapter for Python 3: connection pool
i  python3-psycopg2 - Python 3 module for PostgreSQL
p  python3-psycopg2cffi - implementation of the psycopg2 module using cffi

After uninstalling both:

aptitude search python3-psycopg
p  python3-psycopg - PostgreSQL database adapter for Python 3
p  python3-psycopg-pool - PostgreSQL database adapter for Python 3: connection pool
p  python3-psycopg2 - Python 3 module for PostgreSQL
p  python3-psycopg2cffi - implementation of the psycopg2 module using cffi

I get this error running the community.postgresql.postgresql_info module:

TASK [postgresql : Gather PostgreSQL info] *************************************************************************************************************************************************************************************************************************************
task path: /home/chris/webarch/postgresql/tasks/info.yml:40
fatal: [nextcloud.webarch.org.uk]: FAILED! => 
    changed: false
    invocation:
        module_args:
            ca_cert: null
            connect_params: {}
            db: postgres
            filter:
            - databases
            - roles
            - version
            login_db: postgres
            login_host: ''
            login_password: ''
            login_unix_socket: ''
            login_user: postgres
            port: 5432
            session_role: null
            ssl_cert: null
            ssl_key: null
            ssl_mode: prefer
            trust_input: true
    msg: Failed to import the required Python library (psycopg2) on nextcloud.webarch.org.uk's
        Python /usr/bin/python3. Please read the module documentation and install it in
        the appropriate location. If the required library is installed, but Ansible is
        using the wrong Python interpreter, please consult the documentation on ansible_python_interpreter

So I'm afraid I'm unable to replicate the original error, so I'm afraid I don't think I'm in a position to test the patches in a meaningful way?

Or should I roll back to community.postgresql version 3.9.1 locally to see if that triggers the error?