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?

7 comments
Comment actions Permalink

Solved it by setting prepareThreshold=-1 in the 'Advanced' tab of the data source configuration window.

4
Comment actions Permalink

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.

4
Comment actions Permalink

Hi,

Could you describe how IDE should work with pgBouncer in correct way? Since there is a workaround, it's good news.

Thank you.

0
Comment actions Permalink

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.

0
Comment actions Permalink

Hi,

Why do you need to manage your database via pgBouncer?
Why don't you connect directly to your master node?
Thank you.

 

0
Comment actions Permalink

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.

0
Comment actions Permalink

Can you please set prepareThreshold=0 in the 'Advanced' tab of the data source configuration window?

So data editor should work then.

0

Please sign in to leave a comment.