Missing psycopg2.extras import

[Joris-van-der-Wel]

SUMMARY

Attempting to use the task postgresql_query results in an exception "module 'psycopg2' has no attribute 'extras'".

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_query

ANSIBLE VERSION

ansible [core 2.12.6]
  config file = None
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.9/dist-packages/ansible
  ansible collection location = /home/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.9.2 (default, Feb 28 2021, 17:03:44) [GCC 10.2.1 20210110]
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

# /usr/local/lib/python3.9/dist-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.7.2

# /home/user/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 2.1.5

CONFIGURATION

DEFAULT_FORCE_HANDLERS(/my-project/ansible/ansible.cfg) = True
DEFAULT_FORKS(/my-project/ansible/ansible.cfg) = 2
DEFAULT_GATHERING(/my-project/ansible/ansible.cfg) = explicit
DEFAULT_LOG_PATH(/my-project/ansible/ansible.cfg) = /dev/null
DEFAULT_MODULE_PATH(/my-project/ansible/ansible.cfg) = ['/my-project/ansible/library']
DEFAULT_REMOTE_USER(/my-project/ansible/ansible.cfg) = ansible
DEFAULT_ROLES_PATH(/my-project/ansible/ansible.cfg) = ['/my-project/ansible/roles', '/etc/ansible/roles']
DEFAULT_STDOUT_CALLBACK(/my-project/ansible/ansible.cfg) = yaml
INJECT_FACTS_AS_VARS(/my-project/ansible/ansible.cfg) = False
RETRY_FILES_SAVE_PATH(/my-project/ansible/ansible.cfg) = /home/user/.ansible-retry

OS / ENVIRONMENT

Control host:
Debian Bullseye (debian:bullseye docker image)
python3, python3-pip, python3-psycopg2 (2.8.6) installed using apt
ansible installed using pip3 (ansible==5.7.1)
community.postgresql 2.1.5 installed using ansible-galaxy

Managed host:
Debian Buster (EC2 VM)
python3, python3-psycopg2 (2.7.7) installed using apt

STEPS TO REPRODUCE

- postgresql_query:
    login_host: "example.com"
    login_user: "example-user"
    login_password: "example-password"
    port: 5432
    ssl_mode: require
    db: "example-db"
    query: 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'

EXPECTED RESULTS

Query is executed

ACTUAL RESULTS

The full traceback is:
Traceback (most recent call last):
  File "/home/ansible/.ansible/tmp/ansible-tmp-1654252713.6782622-1068-192019440043912/AnsiballZ_postgresql_query.py", line 107, in <module>
    _ansiballz_main()
  File "/home/ansible/.ansible/tmp/ansible-tmp-1654252713.6782622-1068-192019440043912/AnsiballZ_postgresql_query.py", line 99, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/home/ansible/.ansible/tmp/ansible-tmp-1654252713.6782622-1068-192019440043912/AnsiballZ_postgresql_query.py", line 48, in invoke_module
    run_name='__main__', alter_sys=True)
  File "/usr/lib/python3.7/runpy.py", line 205, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.7/runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "/usr/lib/python3.7/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_postgresql_query_payload_69_k5zqt/ansible_postgresql_query_payload.zip/ansible/modules/postgresql_query.py", line 347, in <module>
  File "/tmp/ansible_postgresql_query_payload_69_k5zqt/ansible_postgresql_query_payload.zip/ansible/modules/postgresql_query.py", line 270, in main
AttributeError: module 'psycopg2' has no attribute 'extras'

It seems to me that an import for psycopg2.extras is missing in this file:

community.postgresql/plugins/module_utils/postgres.py

Lines 19 to 22 in a7f68b8

	try:
	import psycopg2
	HAS_PSYCOPG2 = True
	except ImportError:

And indeed if I execute the following, the problem goes away:

sed -i 's/    import psycopg2/    import psycopg2\n    import psycopg2.extras/' ~/.ansible/collections/ansible_collections/community/postgresql/plugins/module_utils/postgres.py

[hunleyd]

thanks for the report @Joris-van-der-Wel ! we'll look into this shortly.

[jchancojr]

Hi @Joris-van-der-Wel

I'm looking into this and will reach back out with my findings. Thanks!

[jchancojr]

Hi again @Joris-van-der-Wel,

Just wondering, is there any reason in particular that you're using the postgresql_query.py module instead of the postgresql_ext.py module?

Would you mind kindly trying with the postgresql_ext.py module and let us know your results? We'll continue to look into this in the meantime.

Thanks!

[Joris-van-der-Wel]

Hi, sorry for the late reply, Monday was a holiday.

There is no particular reason for using postgresql_query over postgresql_ext. I am in the process of upgrading old playbooks to the latest version of ansible. I am not the original author. Perhaps postgresql_ext did not exist a few years ago?

I can verify that the error does not occur when using postgresql_ext, which is a good work around for us. In our codebase postgresql_query is only used to load extensions.

Do note that, if I change the query to something else, for example query: 'SELECT 1', the same error will still occur.

[Andersson007]

@Joris-van-der-Wel hello, thanks for reporting the issue!

I can verify that the error does not occur when using postgresql_ext, which is a good work around for us. In our codebase postgresql_query is only used to load extensions.

I compared the imports in both the modules and there's nothing different between postgresql_ext and _query that could cause the error in the latter. So it's very strange:)
Anyway, would you like to submit a PR with your fix (adding the import)?
We have the Quick-start guide.
If you have no time, please let me know.

Looking forward to your feedback