3

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.

Improve this question
2
  • 1
    I don't think you can. In any case, if the connection is shared, the concept of "second user" does not apply... the connection would already have one user as the current_user. You can show that one transaction can see uncommitted inserts, while all the rest of transactions cannot with just one connection per psql session.
    – joanolo
    Commented Feb 25, 2017 at 13:31
  • 2
    No. Use multiple psql sessions at the same time.
    – Craig Ringer
    Commented Feb 25, 2017 at 14:29

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • The use case for two "users" on the same connection is one that Rails just implemented for tests: github.com/rails/rails/pull/28083. In a web app integration test ("type this, click that, you should see this"), the web server and test run in different threads, but it's desirable for the test code to be able to say "OK, test complete, now roll back the transaction where the web server inserted that record." But that isn't possible unless the test can see the web server's uncommitted transaction, which means they must share a connection. I think Elixir's Phoenix framework does this too.
    – Nathan Long
    Commented Feb 27, 2017 at 15:25
  • The commit you mention is currently suspected of making their testsuite randomly fail, with precisely the kind of error I'd expect from concurrent use of a db connection. Also libpq doc warns against that, see postgresql.org/docs/current/static/libpq-threading.html
    – Daniel Vérité
    Commented Feb 27, 2017 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.