Hey, first off: thanks for this great collection of modules, I find them very useful!
I hope this is quite an easy issue to solve, I think it could work when we simply return the datetime.timedelta as a String.
In the meantime, it is possible to work around this issue by casting to epoch or another date/time type in PostgreSQL.
Please let me know if or how I can be of further help when fixing this.
Here's a playbook which should be able to reproduce the issue. In my project, there are a few members to the database cluster, that's why all tasks are only run once.
The first task (and debug) run fine, but the execution fails on the second usage of postgresql_query.
PLAY [db_hosts] ***********************************************************************************************************************************************
TASK [try selecting interval as epoch] ************************************************************************************************************************
[WARNING]: Database name has not been passed, used default database to connect to.
ok: [node1]
TASK [debug] **************************************************************************************************************************************************
ok: [node1] => {
"interval_epoch_out": {
"changed": false,
"failed": false,
"query": "SELECT EXTRACT(epoch from make_interval(secs => 3));",
"query_result": [
{
"date_part": 3.0
}
],
"rowcount": 1,
"statusmessage": "SELECT 1",
"warnings": [
"Database name has not been passed, used default database to connect to."
]
}
}
TASK [try selecting interval] *********************************************************************************************************************************
[WARNING]: Database name has not been passed, used default database to connect to.
ok: [node1]
TASK [debug] **************************************************************************************************************************************************
ok: [node1] => {
"interval_epoch_out": {
"changed": false,
"failed": false,
"query": "SELECT make_interval(secs => 3);",
"query_result": [
{
"interval": "0:00:03"
}
],
"rowcount": 1,
"statusmessage": "SELECT 1",
"warnings": [
"Database name has not been passed, used default database to connect to."
]
}
}
PLAY RECAP ****************************************************************************************************************************************************
node1 : ok=4 changed=0 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
PLAY [db_hosts] ***********************************************************************************************************************************************
TASK [try selecting interval as epoch] ************************************************************************************************************************
[WARNING]: Database name has not been passed, used default database to connect to.
ok: [node1]
TASK [debug] **************************************************************************************************************************************************
ok: [node1] => {
"interval_epoch_out": {
"changed": false,
"failed": false,
"query": "SELECT EXTRACT(epoch from make_interval(secs => 3));",
"query_result": [
{
"date_part": 3.0
}
],
"rowcount": 1,
"statusmessage": "SELECT 1",
"warnings": [
"Database name has not been passed, used default database to connect to."
]
}
}
TASK [try selecting interval] *********************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: TypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03
fatal: [node1]: FAILED! => {"changed": false, "module_stderr": "Traceback (most recent call last):\n File \"<stdin>\", line 102, in <module>\n File \"<stdin>\", line 94, in _ansiballz_main\n File \"<stdin>\", line 40, in invoke_module\n File \"/usr/lib/python3.8/runpy.py\", line 207, in run_module\n return _run_module_code(code, init_globals, run_name, mod_spec)\n File \"/usr/lib/python3.8/runpy.py\", line 97, in _run_module_code\n _run_code(code, mod_globals, init_globals,\n File \"/usr/lib/python3.8/runpy.py\", line 87, in _run_code\n exec(code, run_globals)\n File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 350, in <module>\n File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 346, in main\n File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2168, in exit_json\n File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2161, in _return_formatted\n File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 426, in remove_values\n File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 403, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03\n", "module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}
NO MORE HOSTS LEFT ********************************************************************************************************************************************
PLAY RECAP ****************************************************************************************************************************************************
node1 : ok=2 changed=0 unreachable=0 failed=1 skipped=0 rescued=0 ignored=0
TASK [try selecting interval] *********************************************************************************************************************************
task path: /home/julian/test_project/postgresql_query_reproducer.yml:16
Using module file /usr/lib/python3.9/site-packages/ansible/modules/database/postgresql/postgresql_query.py
Pipelining is enabled.
<192.168.178.221> ESTABLISH SSH CONNECTION FOR USER: vagrant
<192.168.178.221> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o 'IdentityFile="misc/vagrant_ubuntu_focal/.vagrant/machines/ubuntu-focal-node-1/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=10 -o ControlPath=/home/julian/.ansible/cp/4983164cf0 192.168.178.221 '/bin/sh -c '"'"'sudo -H -S -n -u postgres /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-lvleoubjilhmusqcpwkppbrqwrsisqkl ; /usr/bin/python3'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<192.168.178.221> (1, b'', b'OpenSSH_8.4p1, OpenSSL 1.1.1i FIPS 8 Dec 2020\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for \'final all\' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: not matched \'final\'\r\ndebug2: match not found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1 (parse only)\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug1: configuration requests final Match pass\r\ndebug2: resolve_canonicalize: hostname 192.168.178.221 is address\r\ndebug1: re-parsing configuration\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for \'final all\' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: matched \'final\'\r\ndebug2: match found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug3: expanded UserKnownHostsFile \'~/.ssh/known_hosts\' -> \'/home/julian/.ssh/known_hosts\'\r\ndebug3: expanded UserKnownHostsFile \'~/.ssh/known_hosts2\' -> \'/home/julian/.ssh/known_hosts2\'\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 4 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 308806\r\ndebug3: mux_client_request_session: session request sent\r\ndebug1: mux_client_request_session: master session id: 2\r\nTraceback (most recent call last):\n File "<stdin>", line 102, in <module>\n File "<stdin>", line 94, in _ansiballz_main\n File "<stdin>", line 40, in invoke_module\n File "/usr/lib/python3.8/runpy.py", line 207, in run_module\n return _run_module_code(code, init_globals, run_name, mod_spec)\n File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code\n _run_code(code, mod_globals, init_globals,\n File "/usr/lib/python3.8/runpy.py", line 87, in _run_code\n exec(code, run_globals)\n File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 350, in <module>\n File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 346, in main\n File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2168, in exit_json\n File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2161, in _return_formatted\n File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 426, in remove_values\n File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 403, in _remove_values_conditions\nTypeError: Value of unknown type: <class \'datetime.timedelta\'>, 0:00:03\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 1\r\n')
<192.168.178.221> Failed to connect to the host via ssh: OpenSSH_8.4p1, OpenSSL 1.1.1i FIPS 8 Dec 2020
debug1: Reading configuration data /home/julian/.ssh/config
debug1: /home/julian/.ssh/config line 1: Applying options for *
debug1: Reading configuration data /etc/ssh/ssh_config
debug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0
debug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf
debug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: not matched 'final'
debug2: match not found
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1 (parse only)
debug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config
debug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]
debug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]
debug1: configuration requests final Match pass
debug2: resolve_canonicalize: hostname 192.168.178.221 is address
debug1: re-parsing configuration
debug1: Reading configuration data /home/julian/.ssh/config
debug1: /home/julian/.ssh/config line 1: Applying options for *
debug1: Reading configuration data /etc/ssh/ssh_config
debug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0
debug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf
debug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: matched 'final'
debug2: match found
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1
debug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config
debug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]
debug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]
debug3: expanded UserKnownHostsFile '~/.ssh/known_hosts' -> '/home/julian/.ssh/known_hosts'
debug3: expanded UserKnownHostsFile '~/.ssh/known_hosts2' -> '/home/julian/.ssh/known_hosts2'
debug1: auto-mux: Trying existing master
debug2: fd 4 setting O_NONBLOCK
debug2: mux_client_hello_exchange: master version 4
debug3: mux_client_forwards: request forwardings: 0 local, 0 remote
debug3: mux_client_request_session: entering
debug3: mux_client_request_alive: entering
debug3: mux_client_request_alive: done pid = 308806
debug3: mux_client_request_session: session request sent
debug1: mux_client_request_session: master session id: 2
Traceback (most recent call last):
File "<stdin>", line 102, in <module>
File "<stdin>", line 94, in _ansiballz_main
File "<stdin>", line 40, in invoke_module
File "/usr/lib/python3.8/runpy.py", line 207, in run_module
return _run_module_code(code, init_globals, run_name, mod_spec)
File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
_run_code(code, mod_globals, init_globals,
File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
exec(code, run_globals)
File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 350, in <module>
File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 346, in main
File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2168, in exit_json
File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2161, in _return_formatted
File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 426, in remove_values
File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 403, in _remove_values_conditions
TypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03
debug3: mux_client_read_packet: read header failed: Broken pipe
debug2: Received exit status from master 1
fatal: [node1]: FAILED! => {
"changed": false,
"module_stderr": "OpenSSH_8.4p1, OpenSSL 1.1.1i FIPS 8 Dec 2020\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: not matched 'final'\r\ndebug2: match not found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1 (parse only)\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug1: configuration requests final Match pass\r\ndebug2: resolve_canonicalize: hostname 192.168.178.221 is address\r\ndebug1: re-parsing configuration\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: matched 'final'\r\ndebug2: match found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug3: expanded UserKnownHostsFile '~/.ssh/known_hosts' -> '/home/julian/.ssh/known_hosts'\r\ndebug3: expanded UserKnownHostsFile '~/.ssh/known_hosts2' -> '/home/julian/.ssh/known_hosts2'\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 4 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 308806\r\ndebug3: mux_client_request_session: session request sent\r\ndebug1: mux_client_request_session: master session id: 2\r\nTraceback (most recent call last):\n File \"<stdin>\", line 102, in <module>\n File \"<stdin>\", line 94, in _ansiballz_main\n File \"<stdin>\", line 40, in invoke_module\n File \"/usr/lib/python3.8/runpy.py\", line 207, in run_module\n return _run_module_code(code, init_globals, run_name, mod_spec)\n File \"/usr/lib/python3.8/runpy.py\", line 97, in _run_module_code\n _run_code(code, mod_globals, init_globals,\n File \"/usr/lib/python3.8/runpy.py\", line 87, in _run_code\n exec(code, run_globals)\n File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 350, in <module>\n File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 346, in main\n File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2168, in exit_json\n File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2161, in _return_formatted\n File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 426, in remove_values\n File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 403, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 1\r\n",
"module_stdout": "",
"msg": "MODULE FAILURE\nSee stdout/stderr for the exact error",
"rc": 1
}