Proposal: Warn/error in transaction pooling mode when unsupported features are used

[JelteF]

In transaction pooling there are quite a few Postgres features that are unsupported. Most importantly:

• SQL level prepared statements, i.e. PREPARE/EXECUTE/DEALLOCATE
• LISTEN (NOTIFY is fine)

#972 is using a clever approach to detect that (currently unsupported) DEALLOCATE ALL/DISCARD ALL have been used, without actually needing any query parsing logic. We could use the same approach to detect PREPARE/EXECUTE/DEALLOCATE/LISTEN queries, and warn/error/close the connection. I feel that being loud about bad usage is probably better for the end user than silently accepting things that will almost certainly break.

There are also other features that are not supported in transaction pooling that we cannot detect using this approach, but at least catching some things seems better than catching nothing:

• Session level SET/RESET (of things not in track_extra_parameters or the hardcoded list)
• Session level advisory locks

[kelvich]

Just faced an interesting foot gun:

• run pg_dump on your database though pgbouncer
• pg_dump will issue SET search_path ... that will be ingested in one of the shared connections to postgres
• that will "poison" that connection and some random database queries will fail to find a table

[JelteF]

Yeah this is a known issue, that is tracked here: #452

I responded to that one just now with the current state of possible workarounds.