Go sql package, PostgreSQL and PgBouncer with behavior on retry

Question

Let's imaging that we have PostgreSQL and PgBouncer (with Transaction mode). Also we are planning to execute following transaction:

BEGIN;

UPDATE a ...;
UPDATE b ...;
SELECT c ...;
UPDATE d ...;

COMMIT;

When transaction begins, PgBouncer gives us connection. Then we execute:

UPDATE a; -- successful
UPDATE b; -- successful
SELECT a; -- successful
UPDATE d; -- failed, because PgBouncer restarted.

Then we try to retry using go DB client

UPDATE d;

On the 3rd time we acquire connect and execute query. Will this query executed in the same transaction or it will be executed on the new connection and leads to inconsistent state?

Or every statement executes with some identifier which can say that it is related to some transaction?

Answer 1

I can't be 100% certain since I am not familiar with the internals of PgBouncer or Postgres but it stands to reason that the transaction is bound to a connection since transactions have no identification. So as long as the TCP/SQL connection is not restarted than you should be able to resume. But if any of the applications restart then the transaction is gone.