Postgres connection pool: transaction vs. session

[discostur]

Hi,

we did setup SpiceDB this week on k8s via the operator. We had an external postgresql database with connection pool already running. However we saw a strange behaviour:

We had to configure our postgres connection pool to "session" mode during setup when the migrations were running. If the connection pool was set to "transaction" the migrations were not able to run:

unable to migrate to `add-index-for-transaction-gc` revision: unable to load version from driver: unable to load alembic revision: FATAL: prepared statement name is already in use (SQLSTATE 08P01)

and

FATAL: prepared statement name is already in use (SQLSTATE 08P01)","pid":1672750874,"sql":"SELECT version_num from alembic_version

However if we changed the connection pool to "session" mode the migrations did run without error. Then, after the initial setup we had lots of connection issues and the database connections from SpiceDB were dying all the time. SpiceDB was not usable. Then we change the connection pool back to "transaction" mode and connections were stable.

So is this a expected behaviour that you have to set your postgres connection pool to "session" during setup / migrations and then to "transaction" in normal operating mode? If yes, did i miss the documentation? ;)

Thanks,
Regards
Kilian

[vroldanbet]

Hi, I assume with connection pooler you mean "PgBouncer". Connection poolers were not supported until relatively recently in SpiceDB, as described in #1217. With newer versions incorporating #1994, this should no longer be a problem. If this issue persists, it may be a new problem we are not yet aware of.

[discostur]

@vroldanbet correct, i'm talking about pgbouncer ;) We are using spiceDB v1.42.1 at the moment which should already include the PRs you posted, or?

[vroldanbet]

Correct, it should be available from SpiceDB 1.35.0 on.

It appears to be an oversight in #1994. The migration driver has its own logic to create the connections, and we missed adding the proper configuration method call there. I've opened #2462 to fix it.

Thanks for reporting!

[discostur]

nice, thanks for your fast response ;)