Connect to database through pgbouncer (prepared statement does not exist)
Answered
Hello!
I have a database which is only available though the pgbouncer interface with transaction pooling enabled (no direct connections allowed). This means no prepared statements, no pub/sub etc.
I'm trying to connect to this database using the latest datagrip. The connection itself works, however, I get an error when trying to update schemas:
[26000] org.postgresql.util.PSQLException: ERROR: prepared statement "S_3" does not exist.
ERROR: prepared statement "S_3" does not exist
Apparently, datagrip uses prepared statements... is there any way to disable them?
Please sign in to leave a comment.
Solved it by setting prepareThreshold=-1 in the 'Advanced' tab of the data source configuration window.
However, data view still doesn't work. Even select queries don't work if transaction set to 'auto' =(
Setting TX=manual in the data source configuration window helps.
Hi,
Could you describe how IDE should work with pgBouncer in correct way? Since there is a workaround, it's good news.
Thank you.
Currently, IDE uses prepared statements to display tables and query results. However, prepared statements don't work with pgbouncer: they're lost on transaction finish. So, currently, viewing table works only if tx set to manual; also, refreshing table view fails if transaction was committed. One have to close table view and reopen it again to refresh the data.
I think IDE should have option to disable prepared statements. This mode, when enabled, should force table views to use normal select queries, with limit and offset. When displaying query results, I think it's ok to load either all rows or first 100Mb of rows etc.
Hi,
Why do you need to manage your database via pgBouncer?
Why don't you connect directly to your master node?
Thank you.
Hi,
Because it's the only way I can connect to it, for example. I'm a developer, not dba.
I think such case is common in enterprise.
Thanks for tips in first two comments.
Can you please set prepareThreshold=0 in the 'Advanced' tab of the data source configuration window?
So data editor should work then.