pgwatch2 using pgbouncer problem

[yinan8128]

Hi pgwatch2 team,

As described in #348, once we switch pgwatch2 to pgbouncer transaction mode, as pgbouncer transaction mode doesn't support "prepared statement" then caused "pq: unnamed prepared statement" error.

So we switched pgwatch2 to pgbouncer session mode but faced pgbouncer_pools_client_maxwait_seconds too high problem(please check attached image file), this happened in postgresql replication node and there was very low load in the period, so mostly like caused by pgwatch2 using pgbouncer session mode.

Go uses prepared statements by default, how to configure pgx driver not to use prepared statements maybe can check here, could you please help fix it and then can support pgbouncer transaction mode, thanks

[pashagolub]

Hello,

Thanks for your request.

pgwatch2 doesn't use `pgx but thanks anyway. Would you add some logs for clarifications? I'm afraid I don't understand the screenshot completely.

Thanks in advance!

[yinan8128]

Hello,

Thank you for your reply

pgbouncer_pools_client_maxwait_seconds is a metric of prometheus-pgbouncer-exporter which means "How long the first (oldest) client in queue has waited, in seconds (labels: database, user)", now the metric showed that 120 seconds sometimes

This problem's root caused is using pgbouncer session mode, as pgbouncer transaction mode is much more efficient than pgbouncer session mode, could you please update the driver to support pgbouncer transaction mode please?

Thank you

[pashagolub]

Hey,

We plan to switch to the pgx library in the new major version. However, I cannot provide you with any timeline, unfortunately.

Seems like there is no solution I'm aware of for the current lib/pq library used.

[yinan8128]

@pashagolub

Thank you for the information, any news for pgwatch2 use pgx library please? Thank you

[pashagolub]

Yes, we switched to pgx in the dev branch. However it's not public at the moment