查询具有数字字段的表时出现模块错误

Question

我正在使用postgresql\u查询模块从ansible playbook查询postgres数据库中的表：

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

测试表：

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

当col3中没有数据时，Playbook会成功运行，但当我查询col3中具有某些值的行时，我会得到以下错误：

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
}

我的本地环境详情：

  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)]

如何解决此错误？它是ansible中的一个bug吗

Answer 1

根据a related question，psycopg2将无法保证在float中表示的东西打包在Decimal中。由于ansible for good or bad只是coerces the row into a ^{}，因此无法将结果序列化为JSON，因为Decimal不被JSON编码器理解

如果不需要ansible列（也就是说，SELECT *仅仅是习惯），或者在查询返回它之前在服务器端显式地强制转换它，那么您就必须满足ansible的要求

- 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'"

^{}只是这个答案的一个简短示例，^{}提供了对该列字符串结果表示的更多控制