1

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?

0

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”.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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