postgresql_query is not able use variable list as postgreSQL array

[mullaiarasu]

SUMMARY

I am not able to use the variable list as postgreSQL array argument in the psql query

PostgreSQL accept array in format '{16367,15820,15865,15869}'
On manipulating variable list [6367,15820,15865,15869] into '{16367,15820,15865,15869}' using customized script and when try to pass as argument will receive below error
Unable to pass options to module, they must be JSON serializable: set([15865, 15820, 15869, 16367]) is not JSON serializable"

ISSUE TYPE

fatal: [localhost]: FAILED! => {"changed": false, "msg": "Cannot execute SQL 'SELECT name FROM mm_configuration WHERE configid = ANY (%s)' ['lst2pgarr.stdout_lines[0]']: malformed array literal: \"lst2pgarr.stdout_lines[0]\"\nLINE 1: ... name FROM mm_configuration WHERE configid = ANY ('lst2pgarr...\n                                                             ^\nDETAIL:  Array value must start with \"{\" or dimension information.\n"}

On trying to use the variable list [15865, 15820, 15869, 16367] below error is received

 "Cannot execute SQL 'SELECT name FROM mm_table WHERE configid = ANY (%s)' [['16367', '15820', '15865', '15869']]: operator does not exist: integer = text\nLINE 1: SELECT name FROM mm_configuration WHERE configid = ANY (ARRA...\n

On trying to use the variable string "15865, 15820, 15869, 16367" below error is received

msg": "Cannot execute SQL 'SELECT name FROM mm_table WHERE configid = ANY (%s)' ['16367,15820,15865,15869']: malformed array literal: \"16367,15820,15865,15869\"\nLINE 1: ... name FROM mm_configuration WHERE configid = ANY ('16367,158...\n 
DETAIL:  Array value must start with \"{\" or dimension information.\n"

COMPONENT NAME

lib/ansible/modules/database/postgresql/postgresql_query.py

ANSIBLE VERSION

ansible 2.8.2
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/root/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/site-packages/ansible
  executable location = /bin/ansible
  python version = 2.7.5 (default, Mar 26 2019, 22:13:06) [GCC 4.8.5 20150623 (Red Hat 4.8.5-36)]

CONFIGURATION

OS / ENVIRONMENT

RHEL7

STEPS TO REPRODUCE

using list variable list in positional_args or named_args for postgresql_query to select , update or delete will reproduce the issue

EXPECTED RESULTS

I

ACTUAL RESULTS

[ansibot]

Files identified in the description:
None

If these files are inaccurate, please update the component name section of the description or use the !component bot command.

click here for bot help

[ansibot]

Files identified in the description:

• lib/ansible/modules/database/postgresql/postgresql_query.py

If these files are inaccurate, please update the component name section of the description or use the !component bot command.

click here for bot help

[ansibot]

cc @Andersson007 @Dorn- @amenonsen @andytom @archf @jbscalia @kostiantyn-nemchenko @kustodian @matburt @nerzhul @sebasmannem @tcraxs @wrouesnel
click here for bot help

[Andersson007]

@mullaiarasu , thank you for reporting this!
I’ll try to solve it

[Andersson007]

@mullaiarasu , i've checked it.
If I understood you correctly you tried to do something like the playbook below (got from private email from you to me):

- name: Select query to db acme with positional arguments and non-default credentials
  postgresql_query:
    db: acme
    login_user: django
    login_password: mysecretpass
    query: SELECT * FROM acme WHERE id = %s AND story = ANY %s
    positional_args:
    - 1
    - test,best,rest,

The best way to pass arrays/json/etc. is just to use quotes and braces inside them, e.g.:

positional_args:
- '{1,2,3}'

it works well.
when we pass - 1,2,3 it is not a list, it's presented as a string '1,2,3' (converted by Ansible itself, not by this module).
We could handle this by checking commas and add braces but there may be other cases like passing json as positional args that also may contain commas, e.g. '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}' , etc.
So, the best and simplest way is just to add commas and braces inside them like - '{1,2,3}' for arrays, - '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}' for json, etc..

[Andersson007]

needs_info

[Andersson007]

I added an example to the module documentation #59979

[mullaiarasu]

Hi Andersson,

I appreciate your help you have suggested, also I have already defined the variable like this and found working, this condition works better in static variables but however when you have dynamic variable list from task output the list looks like ['1','2','3'] I am not able to convert to '{1,2,3}'. I was able to format it like 1,2,3 using string manipulation with join command however I am not able to convert ['1','2','3'] into '{1,2,3}' as ansible wont allow to add curly braces as it impacts the formatting itself i manged to string manipulate like [ 1,2,3] but it also don't work. so can you please let me know how should I convert a ansible (python) list into psql array

[Andersson007]

@mullaiarasu , np, i’ll answer a bit later. My phone doesn’t allow me to use correct formatting, sorry. Not near the PC right now.

[Andersson007]

@mullaiarasu , i'm not a guru of jinja template syntax but in pythonic code we could use roughly something like:

>>> my_list = [1, 2, 3]
>>> my_pg_array = '{' + str(my_list).strip('[]') + '}'
>>> my_pg_array
'{1, 2, 3}'

[mullaiarasu]

Hi Andrey, As you have suggested I have created I tired created python script to convert list from [ 1,2,3] to {1,2,3} but when try read that variable into ansible to pass positional args I get an error JINJA format set not defined. I shared different types of error I received in my issue.

…

On Mon, Aug 5, 2019 at 11:59 AM Andrey Klychkov ***@***.***> wrote: @mullaiarasu <https://github.com/mullaiarasu> , i'm not a guru of jinja template syntax but in pythonic code we could use roughly something like: >>> my_list = [1, 2, 3] >>> my_pg_array = '{' + str(my_list).strip('[]') + '}' >>> my_pg_array '{1, 2, 3}' — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#59955?email_source=notifications&email_token=AE4I3RXJETX7JQIR4X4MFMDQDB2B7A5CNFSM4IIWH5ZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3SX5XQ#issuecomment-518356702>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AE4I3RXWURTDFL7HSRPD2ULQDB2B7ANCNFSM4IIWH5ZA> .

[Andersson007]

@mullaiarasu , could you please give the full code and error messages?

initially, I tried to pass - '{1, 2, 3}' to positional_args and it worked:

positional_args:
- '{1, 2, 3}'

Please note that it must be a string.
Firstly try to check the type of your variable by something like:

- debug:
   msg: '{{ your_variable }}'

[Andersson007]

@mullaiarasu

  1 ---
  2 - hosts: all
  3   become: yes
  4   gather_facts: no
  5   serial: 1
  6
  7   vars:
  8     my_var: '{1, 2, 3}'
  9
 10   tasks:
 11   - name: args
 12     postgresql_query:
 13       query: "insert into test_arr (arr) values (%s)"
 14       positional_args:
 15       - '{{my_var|string}}'

we also must convert my_var to string - '{{my_var|string}}' :), look, it works. Otherwise it will be passed as a set
The output of the playbook above:

            "positional_args": [
                "{1, 2, 3}"
            ],
            "query": "insert into test_arr (arr) values (%s)",
            "session_role": null,
            "ssl_mode": "prefer"
        }
    },
    "query": "insert into test_arr (arr) values ('{1, 2, 3}')",
    "query_result": {},
    "rowcount": 1,
    "statusmessage": "INSERT 0 1"
}

waiting for your feedback

[mullaiarasu]

@mullaiarasu

  1 ---
  2 - hosts: all
  3   become: yes
  4   gather_facts: no
  5   serial: 1
  6
  7   vars:
  8     my_var: '{1, 2, 3}'
  9
 10   tasks:
 11   - name: args
 12     postgresql_query:
 13       query: "insert into test_arr (arr) values (%s)"
 14       positional_args:
 15       - '{{my_var|string}}'

we also must convert my_var to string - '{{my_var|string}}' :), look, it works. Otherwise it will be passed as a set
The output of the playbook above:

            "positional_args": [
                "{1, 2, 3}"
            ],
            "query": "insert into test_arr (arr) values (%s)",
            "session_role": null,
            "ssl_mode": "prefer"
        }
    },
    "query": "insert into test_arr (arr) values ('{1, 2, 3}')",
    "query_result": {},
    "rowcount": 1,
    "statusmessage": "INSERT 0 1"
}

waiting for your feedback

Yes by defining the variable '{{my_var|string}}' it works good hack :) but is there also way to a append curly braces in the ansible itself instead of using python script

[Andersson007]

@mullaiarasu , yep, the problem is obviously because:

 [WARNING]: Failure using method (v2_runner_on_ok) in callback plugin
(<ansible.plugins.callback.default.CallbackModule object at
0x7f3a3692afd0>): set([15858, 15979]) is not JSON
serializable

I cought that too, be sure:

1. the variable must be a string like '{15858, 15979}', see my playbook above
2. then also convert it to string by jinja

positional_args:
- '{{your_var|string}}'

[Andersson007]

@mullaiarasu , sorry, i didn't see you added a new comment while i was writing my previous comment

[Andersson007]

@mullaiarasu ,

but is there also way to a append curly braces in the ansible itself instead of using python script

I tried to find something for that before but failed, for example cookiecutter/cookiecutter#11. However it doesn't work for the second brace, I have no clue why.
The current question is related to using jinja so I suggest closing this issue.
Anyway thank you for discussing, we got a lot of experience :)

[mullaiarasu]

Yup you can close the issue , but it would nice if postgresql_query can accept list but I do understand the postgress array is different than a python list.

Thanks for prompt support really appreciate it Let me know you if want me to close the issue

[Andersson007]

Nps, don’t close the issue for a while. I’ll see again maybe this week

[Andersson007]

@mullaiarasu hey, #60559 please, look