PgBouncer and Prepared Statements: Part II

This article is in continuation to PgBouncer and Prepared Statements: Part I in which I have covered the basic workings of prepared statements in PgBouncer and PostgreSQL. As mentioned in Part I of this article, the expectation is that setting the max_prepared_statements config to a non-zero value should make prepared statements work in Pgbouncer. However, there are some limitations here that you need to be aware of before using prepared statements and in this article, I will focus on 2 of these limitations.

How prepared statements work in PostgreSQL?

Before moving into limitations let me cover some more details on how prepared statements work in PostgreSQL

• Prepare Statement

In this step, PostgreSQL also stores the return type for this prepared statements which in this case is three columns(Id: Int, Name: Varchar, Email: Varchar)

• Execute Statement:

Now what happens If we add a new column to the table and try to execute the statement again?

In this step, I have added a new mobile column to the customers table and as we can see execute command this time throws an error. This is because the return type of the prepared statement has now changed from 3 columns to 4 columns.
Note: This error will appear even if we change the column type or drop any column.

So what do we need to fix this, we will have to deallocate the prepared statement, prepare it again, and re-execute the statement. This happens in most of the clients that catch this error and reprepare the statement on Postgres.

Limitations of Pgbouncer prepared statements

1. Pgbouncer does not support raw SQL queries:
   If you plan on using PgBouncer in the production environment you should be aware that PgBouncer does not and will never parse your raw SQL queries, it only intercepts the protocol layer commands. This is an issue with prepared statements as the libPq client does not have a protocol-level command for DEALLOCATE most of the languages using Libpq as the underlying client sends DEALLOCATE as a raw SQL query to PostgreSQL. Let me explain why this causes an issue with the below example:
   Consider below steps:
   a. A prepared statement(P_1) is created by your application
   b. PgBouncer renames this to (PG_1) and sends it to the database
   c. This process continues as you keep on creating new prepared statements
   d. Now after some time you will hit the max number of prepared statements on the application side, and it will issue a raw SQL for “DEALLOCATE P_1”
   e. Now as PgBouncer does not intercept raw SQL it will pass it along to the database
   f. But as the database has no prepared statement named P_1[statement was renamed to PG_1 by pgbouncer] it will throw an error “Prepared Statement P_1 not found”
2. Prepared statements are not prepared again on schema alteration:
   As mentioned above in the prepared statement working section, if there is a schema alteration we need to re-prepare the prepared statements again to make select * prepared statements work. However, similar to how Pgbouncer does not parse RAW SQL queries it also does not parse the error response coming from PostgreSQL and thus does not automatically reprepare the statement.
   So the prepared statement is never prepared again and your EXECUTE command keeps on failing.
   This might even cause your code to get stuck in an infinite loop which I will explain below:
   a. Application prepares a prepared statement P_1 for “select * from customers where id = $1”
   b. Pgbouncer renames the statement to PG_1 and sends it to the database
   c. A new column is added to the customers table and the return type of the prepared statement changes
   d. Now if the application sends a command “EXECUTE P_1”, Pgbouncer changes this to “EXECUTE PG_1” and sends it to the database
   e. Database will throw an error for an invalid prepared statement
   f. As Pgbouncer does not parse error it will return the error to the application
   g. Application tries to re-prepare the statement (“select * from customers where id = $1” as P_2)but as Pgbouncer sees both the SQLs as same it does nothing and keeps prepared statement PG_1 open
   h.Now the application again runs “Execute P_2" and steps from point e continue again causing an infinite loop.

Solutions to the above limitations

1. Pgbouncer does not support raw SQL queries:
   One of the possible solutions to this limitation is to keep the number of prepared statements on your application side and Pgbouncer higher than the total prepared statements needed by your application on a single connection. This ensures that DEALLOCATE is never called and the issue never appears.
   Note: In Libpq 17 there is support for DEALLOCATE using protocol level command. Once it is released you can upgrade your client to use libPq17 and use protocol level command to Deallocate which Pgbouncer will be able to intercept.
2. Prepared statements are not prepared again on schema alteration:
   The only solution for this problem as of now is to do a RECONNECT command on Pgbouncer which will close all server-side connections on Pgbouncer. Whenever a new connection is created the statement is prepared again, which resolves the return type mismatch issue.