search

powa-team / powa-collector

PoWA Collector daemon for remote snapshot
http://powa.readthedocs.io/
PostgreSQL License
7 stars 4 forks source link

standard_conforming_strings = off not supported #1

Closed milanju closed 5 years ago

milanju commented 5 years ago

When having standard_conforming_strings = off on remote database the following query fails:

SELECT
(pg_catalog.regexp_split_to_array(extversion, '\.'))[1]::int,
extversion
FROM pg_catalog.pg_extension
WHERE extname = 'powa'

with error DataError: invalid input syntax for integer: "" (https://github.com/powa-team/powa-collector/blob/master/powa_collector/powa_worker.py#L72) due to regexp needing double escape when standard_conforming_strings is off ('\.'). I have not found any way to solve this issue without turning the setting on or modifying powa-collector src. Result is the collector stopping for the given db (it does not collect, in web ui it shows stopped as status)

Not relying on regexp would be an option, e.g. exchange (pg_catalog.regexp_split_to_array(extversion, '\.'))[1]::int with (split_part(extversion, '.', 1))::int

To reproduce: Execute the query within postgres (I used 9.5.17) while having configured standard_conforming_strings = off.

rjuju commented 5 years ago

Hi,

Yes indeed that's quite broken. I actually didn't know split_part (shame on me), so that's indeed a very good alternative! I'll push a fix soon, unless you want to propose a PR?

In the meantime, a workaround could be to use a different user for the collector, and enable standard_conforming_strings for this user in this database only (eg. ALTER USER powa_role IN DATABASE powa SET standard_conforming_strings = on)

milanju commented 5 years ago

The workaround you suggest seems great for the meantime, thanks! I opened a PR anyways, maybe it saves someone some headaches later down the road :).

rjuju commented 5 years ago

Great! I just merged your PR, I keep this bug opened until I release 0.0.3 in case someone runs into the same issue.

rjuju commented 5 years ago

I just pushed release 0.0.3, which includes this fix!