Postgresql – have two `psql` sessions use the same database connection

postgresqlpsql

Is it possible to open two psql sessions that use the same database connection?

My goal is to show that a second user on the same connection can see uncommitted INSERTs, wherease a user on a different connection can't see INSERTs until the first connection runs COMMIT on its transaction.

Best Answer

Is it possible to open two psql sessions that use the same database connection?

In theory it's possible through connection sharing. Instead of connecting directly to PostgreSQL, psql would connect to a middleware such as pgBouncer, which has the ability to reuse the same backend connection across its multiple clients.

My goal is to show that a second user on the same connection can see uncommitted INSERTs,

TL;DR: forget that, run the SELECT in the same psql session than the INSERT.

From the point of view of PostgreSQL, there is no second user, it's the same user on the same connection, which is seeing the effects of its own not-yet-committed INSERTs. Otherwise it would be a dirty read, which PostgreSQL documentation states as "Not possible" in any of the isolation levels.

A middleware routing queries from different connections from its clients to the same connection to the backend could produce the effect of a dirty read to its client. In practice, your goal is out of reach with a normal pooler, because these tools actively forbird situations where a client would see uncommitted changes of another client. It's part of the effort to be transparent to users, to whom the results should be the same whether their connection is shared or not.

For example PgBouncer when using statement pooling, says:

Statement pooling Most aggressive method. This is transaction pooling with a twist - multi-statement transactions are disallowed. This is meant to enforce "autocommit" mode on client, mostly targeted for PL/Proxy.

The mentioned demo would need statement pooling in a multi-statement transaction. So in order to make it work, you'd need to hack pgBouncer to bypass that check. That's technically possible, but in the end it would demonstrate a setup that never exists in practice. In this sense, it has more potential to confuse people rather than to help them understanding how isolation works.

Related Question