Module error while querying table having numeric field

user_2527245 0
Viewed 439

I'm querying a table in postgres database from ansible playbook using postgresql_query module:

- name: Query table
  postgresql_query:
    db: "db"
    login_host: "host"
    login_user: "user"
    login_password: "pass"
    query: "SELECT * FROM test WHERE col1 = 'test_col'"

test table:

CREATE TABLE test (
  col1 VARCHAR(75),
  col2 VARCHAR(75),
  col3 NUMERIC,
  PRIMARY KEY (col1, col2));
)

Playbook runs successfully when there is no data in col3, but when i query for rows having some value in col3 i get below error:

The full traceback is:
Traceback (most recent call last):
  File "/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py", line 102, in <module>
    _ansiballz_main()
  File "/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_query', init_globals=None, run_name='__main__', alter_sys=True)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 176, in run_module
    fname, loader, pkg_name)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 82, in _run_module_code
    mod_name, mod_fname, mod_loader, pkg_name)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 350, in <module>
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 346, in main
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2072, in exit_json
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2065, in _return_formatted
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 418, in remove_values
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 401, in _remove_values_conditions
TypeError: Value of unknown type: <class 'decimal.Decimal'>, 1001

fatal: [localhost]: FAILED! => {
    "changed": false, 
    "module_stderr": "Traceback (most recent call last):\n  File \"/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py\", line 102, in <module>\n    _ansiballz_main()\n  File \"/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py\", line 94, in _ansiballz_main\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n  File \"/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py\", line 40, in invoke_module\n    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_query', init_globals=None, run_name='__main__', alter_sys=True)\n  File \"/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py\", line 176, in run_module\n    fname, loader, pkg_name)\n  File \"/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py\", line 82, in _run_module_code\n    mod_name, mod_fname, mod_loader, pkg_name)\n  File \"/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py\", line 72, in _run_code\n    exec code in run_globals\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 350, in <module>\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 346, in main\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2072, in exit_json\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2065, in _return_formatted\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 418, in remove_values\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 401, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'decimal.Decimal'>, 1001\n", 
    "module_stdout": "", 
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", 
    "rc": 1
}

My local env details:

  ansible 2.9.1
  config file = /Users/ss/Work/doaa/git/Server-DECOM/ansible.cfg
  configured module search path = [u'/Users/ss/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /Library/Python/2.7/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 2.7.10 (default, Feb 22 2019, 21:55:15) [GCC 4.2.1 Compatible Apple LLVM 10.0.1 (clang-1001.0.37.14)]

How can I resolve this error? Is it a bug in ansible?

ansible python
1 Answers

According to a related question, psycopg2 packages things in Decimal that can't be guaranteed to be represented in float. Since ansible -- for good or bad -- just coerces the row into a dict, it is then unable to serialize the result into JSON since Decimal isn't understood by the JSON encoder.

You'll have to meet ansible half-way by either omitting that column if you don't need it (that is, SELECT * was merely habit), or explicitly cast it on the server-side before the query returns it

- name: Query table
  postgresql_query:
    db: "db"
    login_host: "host"
    login_user: "user"
    login_password: "pass"
    query: "SELECT col1, col2, CAST(col3 AS TEXT) FROM test WHERE col1 = 'test_col'"

CAST was just a short example for this answer, to_char offers a lot more control over the representation of that column's string result

user_2250160
Related