postgresql_script doesn't like psql-specific commands

[hunleyd]

SUMMARY

When running pg_upgrade, PG will create a file called update_extensions.sql that you're supposed to use to upgrade all the extensions after the upgrade. However, passing this script to postgresql_script fails with the following:

  192.168.122.36 failed | msg: Cannot execute SQL '\connect postgres
ALTER EXTENSION "pg_stat_statements" UPDATE;
' None: syntax error at or near "\"
LINE 1: \connect postgres
        ^
  Ran out of hosts!

It doesn't like the \connect which is a psql-ism. I'm not sure if this a bug in the module, or in psycopg2.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_script

ANSIBLE VERSION

2.16.2

COLLECTION VERSION

community.postgresql 2.4.5

CONFIGURATION

ANSIBLE_NOCOWS(/home/doug/.ansible.cfg) = True
CONFIG_FILE() = /home/doug/.ansible.cfg
DEFAULT_FORKS(/home/doug/.ansible.cfg) = 32
DEFAULT_GATHERING(/home/doug/.ansible.cfg) = smart
DEFAULT_INTERNAL_POLL_INTERVAL(/home/doug/.ansible.cfg) = 0.001
DEFAULT_LOAD_CALLBACK_PLUGINS(/home/doug/.ansible.cfg) = True
DEFAULT_MANAGED_STR(/home/doug/.ansible.cfg) = "$Ansible {{{{ template_path|basename + lookup('pipe', 'git log --form>
DEFAULT_PRIVATE_KEY_FILE(/home/doug/.ansible.cfg) = /home/doug/.local/share/vagrant/insecure_private_key
DEFAULT_REMOTE_USER(/home/doug/.ansible.cfg) = vagrant
DEFAULT_STDOUT_CALLBACK(/home/doug/.ansible.cfg) = unixy
DEPRECATION_WARNINGS(/home/doug/.ansible.cfg) = False
DISPLAY_SKIPPED_HOSTS(/home/doug/.ansible.cfg) = False
EDITOR(env: EDITOR) = nvim
ENABLE_TASK_DEBUGGER(/home/doug/.ansible.cfg) = False
HOST_KEY_CHECKING(/home/doug/.ansible.cfg) = False
INJECT_FACTS_AS_VARS(/home/doug/.ansible.cfg) = False
INTERPRETER_PYTHON(/home/doug/.ansible.cfg) = auto_silent
PAGER(env: PAGER) = less
RETRY_FILES_ENABLED(/home/doug/.ansible.cfg) = False
USE_PERSISTENT_CONNECTIONS(/home/doug/.ansible.cfg) = True

OS / ENVIRONMENT

This was Rocky 9 upgrading PG from 12.x to 13.x

STEPS TO REPRODUCE

see summary

EXPECTED RESULTS

Would like postgresql_script to not error if possible.

ACTUAL RESULTS

see summary

[Andersson007]

@hunleyd as i can see in the code, the module:

1. creates a file object using the file we pass
2. reads it with .read(), i.e. the whole content at once
3. feeds it to the cursor.execute() method w/o any intermediate transformations

Probably yes, the driver just doesn't understand the \connect postgres psql-ism, it's probably supposed to understand only standard queries.
I believe we shouldn't modify the files on the module side.
There's already a warning in the module description but it's only about dump files. I think we could generalize it more saying something like Do not use it with files made by PostgreSQL tools like pg_dump or pg_upgrade....
What do you think?