Postgres - ERROR: prepared statement "S_1" already exists

Question

When executing batch queries via JDBC to pgbouncer, I get the following error:

org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists

I've found bug reports around the web, but they all seem to deal with Postgres 8.3 or below, whereas we're working with Postgres 9.

Here's the code that triggers the error:

this.getJdbcTemplate().update("delete from xx where username = ?", username);

this.getJdbcTemplate().batchUpdate( "INSERT INTO xx(a, b, c, d, e) " + 
                "VALUES (?, ?, ?, ?, ?)", new BatchPreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        ps.setString(1, value1);
        ps.setString(2, value2);
        ps.setString(3, value3);
        ps.setString(4, value4);
        ps.setBoolean(5, value5);
    }
    @Override
    public int getBatchSize() {
        return something();
    }
});

Anyone seen this before?

Edit 1:

This turned out to be a pgBouncer issue that occurs when using anything other than session pooling. We were using transaction pooling, which apparently can't support prepared statements. By switching to session pooling, we got around the issue.

Unfortunately, this isn't a good fix for our use case. We have two separate uses for pgBouncer: one part of our system does bulk updates which are most efficient as prepared statements, and another part needs many connections in very rapid succession. Since pgBouncer doesn't allow switching back and forth between session pooling and transaction pooling, we're forced to run two separate instances on different ports just to support our needs.

Edit 2:

I ran across this link, where the poster has rolled a patch of his own. We're currently looking at implementing it for our own uses if it proves to be safe and effective.

Mainspring

Maybe a pgbouncer problem? Or an outdated JDBC driver? – Sheeting

It's definitely a pgbouncer problem. We bypassed pgbouncer and hit Postgres directly, and the prepared statement works. The JDBC driver is the latest available. – Mainspring

Is there some setting that tells pgbouncer to reset connections on release back to the pool? Sounds like you're reusing connetions that already have run the prepare before. – Robers

@ScottMarlowe there is. It's the connection pooling option. We use transaction pooling, which is supposed to release connections back to the pool immediately after a transaction ends. – Mainspring

It's not about releasing the connection, it's about resetting it. If it doesn't do a reset on the connection then old things like prepared queries can still be there. – Robers

@ScottMarlowe how might that be achieved? – Mainspring

Take a look here: pgpool.projects.postgresql.org/pgpool-II/doc/… Basically it depends on pg version, but ABORT; DISCARD ALL; will work for all versions from 8.3 onward – Robers

prepareThreshold=0 solved my issue. – Ore

It was indeed a PgBouncer problem, but that problem is now fixed since version 1.21.1 you can set max_prepared_statements to a non-zero value in the PgBouncer configuration and everything will work correctly. – Outlandish

Answer 1

This turned out to be a pgBouncer issue that occurs when using anything other than session pooling. We were using transaction pooling, which apparently can't support prepared statements. By switching to session pooling, we got around the issue.

Unfortunately, this isn't a good fix for our use case. We have two separate uses for pgBouncer: one part of our system does bulk updates which are most efficient as prepared statements, and another part needs many connections in very rapid succession. Since pgBouncer doesn't allow switching back and forth between session pooling and transaction pooling, we're forced to either run two separate instances on different ports just to support our needs, or to implement this patch. Preliminary testing shows it to work well, but time will tell if it proves to be safe and effective.

Mainspring

The link is dead, unfortunately. This is an archive snapshot: web.archive.org/web/20120222062130/http://pgfoundry.org/… – Marcelina

Could you create a separate pool for your bulk inserts in this case that uses session pooling and use the transaction pooling in another location or does this have some other negative consequence? – Deceive

Answer 2

Disabling prepared statements in JDBC. The proper way to do it for JDBC is adding "prepareThreshold=0" parameter to connect string.

jdbc:postgresql://ip:port/db_name?prepareThreshold=0

Thaumaturge

worked for me - but perhaps it would be better to remove useAffectedRows=true from your answer? not necessary? – Jasun

OK，just removed it – Thaumaturge

Answer 3

New, Better Answer

To discard session state and effectively forget the "S_1" prepared statement, use server_reset_query option in PgBouncer config.

Old Answer

See http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_prepared_statements_with_transaction_pooling

Switching into session mode is not an ideal solution. Transacion pooling is much more efficient. But for transaction pooling you need stateless DB calls.

I think you have three options:

1. Disable PS in jdbc driver,
2. manually deallocate them in your Java code,
3. configure pgbouncer to discard them on transaction end.

I would try option 1 or option 3 - depending on actual way in which your app uses them.

For more info, read the docs:

http://pgbouncer.projects.postgresql.org/doc/config.html (search for server_reset_query),

or google for this:

postgresql jdbc +preparethreshold

Hauck

"When transaction pooling is used, the server_reset_query should be empty, as clients should not use any session features." Doesn't look like option 3 is possible. Option 1 isn't feasible for our use. Option 2 could possibly work, but how might that be accomplished? – Mainspring

It appears that session_lifetime may be the setting that causes the problem. We're experimenting with it now. I'll report back shortly. – Mainspring

@Chris: option 3 is possible - pgbouncer doc says what SHOULD be done in general, but in your case I would try it. Regarding option 2, see commandprompt.com/ppbook/x20921, and regarding option 1 - why not? – Hauck

new link for server_reset_query docs : pgbouncer.github.io/faq.html#what-should-my-serverresetquery-be – Dipterous

Answer 4

In our case the issue was not related to pgbouncer. Since we did were not able to append prepareThreshold=0 to the URL what we did to fix was the following

View the prepared statements

select * from pg_prepared_statements;

Deallocate the faulty table

select * from pg_prepared_statements;
deallocate "S_1";

Nightdress

Answer 5

I had this problem, we have pgbouncer configurated in transaction level, we were using psql 11.8, we just upgraded the psql jar to the latest version, it got fixed.

Cook

Please post the answers to particular version of psql which ever user is asking because upgrading the psql may cause other issues and it would be difficult for user to understand. and try to correct/improvise the code which ever is posted in question – Ablative

Thanks for the feedback! - Dilip D I have upgraded to postgres.jdbc.4.2-42..2.14 from postgres 8.4 – Cook

Answer 6

Since PgBouncer 1.21.0 it supports protocol level named prepared statements in transaction pooling mode. By changing the max_prepared_statements setting to a non-zero value in the PgBouncer configuration file, you can turn this support on. That should make these errors go away, and allow yo to run only a single instance of PgBouncer. For details check the docs: https://www.pgbouncer.org/config.html#max_prepared_statements

Outlandish