Random "prepared statement ... does not exist" errors during database deployment on Azure Cosmos DB for PostgreSQL with managed PgBouncer.

[zxli]

Search first

• I searched and no similar issues were found

Description

During database deployment on Azure Cosmos DB for PostgreSQL, Liquibase failed randomly with "prepared statement ... does not exist" when the managed PgBouncer is used. The chance of the issue happening is low in general. But under some circumstances, it could happen much more frequently. Based on my experience, it happened much more frequently when Liquibase runs in a different region than where the database is located.

Azure Cosmos DB for PostgreSQL supports managed PgBouncer. PgBouncer will be used when using port 6432 to connect to the database.

Liquibase uses prepared statements on PostgreSQL. Prepared statements wouldn't work well with PgBouncer if connection pooling mode is not set to "session". Here is a blog about it.

PgBouncer supports three modes for connection pooling controlled by parameter pool_mode. The default mode is "session". But the default mode on Azure Cosmos DB for PostgreSQL is TRANSACTION. As of this writing, Azure Cosmos DB for PostgreSQL doesn't support "session" mode. A simple workaround is to bypass PgBouncer by using port 5432 instead of 6432 on Azure Cosmos DB for PostgreSQL.

The ask is to update Liquibase's document for PostgreSQL to include the information about this potential issue and how to mitigate it. This issue may have contributed to some other issues like this. It could also happen on other managed PostgreSQL services or wherever PgBouncer is used when the pooling mode is not set to "session".

Steps To Reproduce

The chance of this issue happening is low in general.

1. Create an Azure Cosmos DB for PostgreSQL cluster.
2. Use the connection string with PgBouncer (port 6432) to connect to the database in Liquibase.
3. Use Liquibase to deploy the database on the Azure Cosos DB for PostgreSQL cluster. Sometimes the deployment fails with "prepared statement ... does not exist" error.

Expected/Desired Behavior

The database deployment should succeed without random "prepared statement ... does not exist" error.

Liquibase Version

No response

Database Vendor & Version

Azure Cosmos DB for PostgreSQL V16

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @zxli, thank you for looking into this problem and providing us with such a detailed explanation.
Our documentation will be updated to let users know about the issue and how to work around it.

Thanks again!
Tatiana