Issue when trying to activate postgis extension

[rdehouss]

SUMMARY

I'm trying to install postgis via the postgresql_ext module without success.
Indeed, there is an exception at this line

The reason is that there is a version unpackaged for postgis (at least, in docker-postgis)

SELECT * FROM pg_available_extension_versions WHERE name = 'postgis';
  name   |   version    | installed | superuser | trusted | relocatable | schema | requires |                          comment                           
---------+--------------+-----------+-----------+---------+-------------+--------+----------+------------------------------------------------------------
 postgis | 3.1.0dev     | t         | t         | f       | f           |        |          | PostGIS geometry and geography spatial types and functions
 postgis | 3.1.0devnext | f         | t         | f       | f           |        |          | PostGIS geometry and geography spatial types and functions
 postgis | unpackaged   | f         | t         | f       | f           |        |          | PostGIS geometry and geography spatial types and functions
(3 rows)

and in python3:

from distutils.version import LooseVersion
v0 = LooseVersion('0')
vX = LooseVersion('unpackaged')
vX > v0

will give

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.8/distutils/version.py", line 64, in __gt__
    c = self._cmp(other)
  File "/usr/lib/python3.8/distutils/version.py", line 337, in _cmp
    if self.version < other.version:
TypeError: '<' not supported between instances of 'str' and 'int'

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_ext

ANSIBLE VERSION

ansible 2.10.1
  config file = /ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.8/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.8.2 (default, Jul 18 2020, 19:35:03) [GCC 9.2.0]

CONFIGURATION

[DEPRECATION WARNING]: DEFAULT_HASH_BEHAVIOUR option, This feature is fragile and not portable, leading to continual confusion and misuse , use the ``combine`` filter explicitly instead. This feature will be removed in version 2.13. Deprecation warnings can be 
disabled by setting deprecation_warnings=False in ansible.cfg.
COLLECTIONS_PATHS(/ansible/ansible.cfg) = ['/ansible/collections', '/ansible/collections/vendor']
DEFAULT_HASH_BEHAVIOUR(/ansible/ansible.cfg) = merge
DEFAULT_HOST_LIST(/ansible/ansible.cfg) = ['/ansible/inventory.yml']
DEFAULT_ROLES_PATH(/ansible/ansible.cfg) = ['/ansible/roles', '/ansible/roles/vendor']
DEFAULT_STDOUT_CALLBACK(/ansible/ansible.cfg) = full_skip
HOST_KEY_CHECKING(/ansible/ansible.cfg) = False
INTERPRETER_PYTHON(/ansible/ansible.cfg) = auto_silent

OS / ENVIRONMENT

Ansible in Docker

STEPS TO REPRODUCE

- name: CREATE EXTENSION postgis
  community.general.postgresql_ext:
    login_host: "localhost"
    db: mydb
    version: latest
    name: postgis
    state: "present"

EXPECTED RESULTS

The version "unpackaged" is either ignored or the exception is catched and a specific option is available to install the unpackaged version (Note that it's not possible anymore since pg 13 I think, see [pg 12 createextension doc)(https://www.postgresql.org/docs/12/sql-createextension.html) and [pg 13 createextension doc)(https://www.postgresql.org/docs/13/sql-createextension.html)

ACTUAL RESULTS

The full traceback is:
Traceback (most recent call last):
  File "/tmp/ansible_community.general.postgresql_ext_payload__8anp8d6/ansible_community.general.postgresql_ext_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ext.py", line 344, in main
  File "/tmp/ansible_community.general.postgresql_ext_payload__8anp8d6/ansible_community.general.postgresql_ext_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ext.py", line 291, in ext_get_versions
  File "/tmp/ansible_community.general.postgresql_ext_payload__8anp8d6/ansible_community.general.postgresql_ext_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ext.py", line 292, in <listcomp>
  File "/usr/lib/python3.7/distutils/version.py", line 64, in __gt__
    c = self._cmp(other)
  File "/usr/lib/python3.7/distutils/version.py", line 337, in _cmp
    if self.version < other.version:
TypeError: '<' not supported between instances of 'str' and 'int'
failed: [db-001] (<REDACTED>) => {
    "ansible_loop_var": "item",
    "changed": false,
    "invocation": <REDACTED>,
    "item": <REDACTED>,
    "msg": "Database query failed: '<' not supported between instances of 'str' and 'int'"
}

[ansibullbot]

Files identified in the description:

• plugins/modules/database/postgresql/postgresql_ext.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

[ansibullbot]

cc @Andersson007 @Dorn- @andytom @dschep @ilicmilan @jbscalia @kostiantyn-nemchenko @matburt @nerzhul @sebasmannem @strk @tcraxs
click here for bot help

[Andersson007]

@rdehouss hey, thanks for reporting the issue!

Folks, if anyone wants to fix this, please put the comment here or i'll try to solve this myself ASAP.

[Andersson007]

I've just begun

[Andersson007]

@rdehouss done #1099 please take a look and please put shipit in the PR if it's ok

[Andersson007]

@rdehouss thanks again for reporting this!
The fix will be released with community.general 1.3.0 (~the second half of November) and published in galaxy, so after that you can install the collection separately from there