Closed jbisabel closed 3 months ago
datestyle = 'redwood'
is only a thing in EDB's fork of Postgres, and is meant to show dates in an Oracle-compatible format. I defer to @Andersson007 , but I don't see us supporting fork changes like this.
Wouldn't it be best practice to set a datestyle on the connection? For example patching in the following snippet in the connect_to_db(...)
function in postgres.py
bypasses the issue:
cursor = db_connection.cursor(row_factory=psycopg.rows.dict_row)
try:
cursor.execute('SET datestyle TO iso')
except Exception as e:
module.fail_json(msg="Could not set date style: %s" % to_native(e))
finally:
cursor.close()
Though that does seem to subsequently land me in a different can of worms with the python conversion of infinity
"Cannot execute SQL 'SELECT r.rolname, r.rolsuper, r.rolcanlogin, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members AS m JOIN pg_catalog.pg_roles AS b ON (m.roleid = b.oid) WHERE m.member = r.oid) AS memberof FROM pg_catalog.pg_roles AS r WHERE r.rolname !~ '^pg_'': timestamp too large (after year 10K): 'infinity'"
The latter seems to be a known psycopg thing, going by the docs here. Inserting the following snippet in postgres.py
fixes it at least for the info module...
class InfTimestamptzLoader(TimestamptzLoader):
def load(self, data):
if data == b"infinity":
return datetime.max
elif data == b"-infinity":
return datetime.min
else:
return super().load(data)
psycopg.adapters.register_loader("timestamptz", InfTimestamptzLoader)
@jbisabel hello, thanks for reporting the issue @hunleyd i incline to agree that we shouldn't support forks in general. Though, if this is the only thing preventing EDB users to use the collection, maybe we should allow it provided that it does not break things for postgres users? This is the first report specific to a fork i've ever seen here (i.e. in the last 5 years), so not sure if there'll be an influx of them if we make a precedent. I'm not trying to persuade to do it, really don't know. I would recommend interested parties to create a fork of this collection for EDB things but wouldn't it be an overkill.
What do you think @hunleyd @jbisabel @aleksvagachev @betanummeric @jchancojr ?
UPDATE: i think we should run any fork specific code only when that fork runs (to identify a server implementation and use if srv_implementation == 'EDB'
to run that code and we definitely shouldn't run EDB or whatever non postgres in our CI. Considering this, it'd be impossible to test such changes..
First off I propose to split off the infinity issue into a separate issue, since from my testing it seems to be independent of this one, I can reproduce it using the default postgres datestyle.
Regarding datestyle, I'm kind of the opinion that setting it on the connection is a good idea in general, regardless of whether you're talking to a standard postgres or whatever fork, just to make sure you're getting a consistent representation across your playbook run. If not you run the risk of encountering different datestyles depending on whatever is set in the postgres.conf you're currently talking to.
SUMMARY
When trying to fetch roles info, create users,... the module chokes on timestamps when
datestyle = 'redwood,show_time'
in the postgresql.conf of the databaseISSUE TYPE
COMPONENT NAME
postgresql_info
ANSIBLE VERSION
COLLECTION VERSION
CONFIGURATION
OS / ENVIRONMENT
STEPS TO REPRODUCE
EXPECTED RESULTS
ACTUAL RESULTS