Postgresql – Reusing the same connection to read uncommited transaction

pgbouncerpostgresqltransaction

My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging

What I tried:

  1. I start a pgbouncer instance, and set the max-db-connection to 1
  2. From my node application, i connect to pg-bouncer
  3. I BEGIN a transaction, and inserted a few rows, i.e: INSERT INTO USERS VALUES ('john')
  4. I end the application without COMMIT
  5. I connect to pgbouncer from another client (i.e: Datagrip)

Expected: I can continue from the uncommitted transaction, and SELECT * FROM USERS WHERE name = 'john', would retrieve the rows.

But what happens is that PgBouncer would close the connection ( closing because: unclean server (age=0s)) when a client connects, since it knows that there is an uncommited transaction. Is there a way to make this hack work with/without using PgBouncer?

Best Answer

You can only see uncommitted data modifications withing the transaction that performed them, so your client code would have to use the same transaction for everything. This sounds a lot like you are going to end up with long running transactions, which is a very bad idea in database, particularly in PostgreSQL.

I recommend that you approach this in a different way. Add a column committed to the table that you set to TRUE or FALSE (or something similar). Then you don't have to keep transactions open for a long time, and other transactions that choose to do so can simply SELECT “uncommitted data”.

Related Question