max_prepared_statements and names of prepared statements

[janpio]

1.21.0 added support for named prepared statements, yay!

So I have been reading https://www.pgbouncer.org/config.html#max_prepared_statements to try to understand if this will work with the PostgreSQL driver we are using. It names (named prepared) statements with s0, s1 and so on.

Do I understand correctly that the new prepared statements mode will have trouble with such a naming pattern, if 2 instances of the app for example prepare 2 different queries both with the name s0? Do I understand correctly that PgBouncer would treat them the same and try to use the already prepared statement, which then would be incorrect for the second instance?

[JelteF]

That should work totally fine. PgBouncer deduplicates prepared statements based on the actual query that is being prepared. So even if the clients do the following your code will work fine:
client 1:

• s1: SELECT 1;
• s2: SELECT 2;

and in client 2:

• s1: SELECT 2;
• s2: SELECT 1;

[janpio]

Ok, that is cool - I did not get that it looks at the actual query as well and thought it was just using the name of the statement.

I'll have to look at the code again to understand exactly how and where that happens.

[JelteF]

The specific code is this: https://github.com/pgbouncer/pgbouncer/blob/master/src/prepare.c#L123-L127

Basically it keeps a global hashmap that maps query_string_and_argument_types of each prepared statement that the client sends to a unique ID. This unique ID is then used to prepare a statement on the server with the name PGBOUNCER_{unique_id} (e.g. PGBOUNCER_123), or reuse it if it was already prepared before on that server, possibly by another client.

[janpio]

Thanks, that it looks at the query string is amazing and will most probably make our use case work.
Now trying to back that up with tests.