Postgres transactions with persistent connection

Question

So I have a program written in NodeJS, that has a pool of persistent connections, all works as expected except when I execute queries similar to the following:

BEGIN;
  INSERT INTO user(id, name) VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

If the transaction succeeds then all is good, but if it fails e.g. due to unique constraint on (auth_id, user_id) then the first time with throw the error I would expect due to the unique constraint, however if I attempt to run any other query (whether I expect it to succeed or fail) I get the following error:

ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

And the only way to fix it is to close and reopen the database connection. It would seem that the transaction is still somewhat left hanging in some way. The connection state gets stuck as idle in transaction (aborted). Is it normal that only one begin/commit transaction can be used per connection or is there some way to clear the transaction without having to re-establish a connection?

Answer 1

Conceptually, one connection can deal with only one transaction at a time; but you can have more than one in a single session, as far as you commit (or rollback) your transaction before you start a new one.

This would be an example that works:

CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Some connection tools (for instance, pgAdmin in default configuration) will have an autocommit feature (in pgAdmin, you control it by means of menu checks at "Query > AutoCommit" and "Query > AutoRollback"). If these are checked, a transaction that fails would be AutoRollbacked. If you disable these checks, the next example would behave like you indicate:

DROP TABLE IF EXISTS "user" ;
DROP TABLE IF EXISTS login ;
CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

-- This transaction block will work
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- This one will not. As AutoRollback is off, the transaction won't be 
-- commited (because of the error) nor Rolled Back 
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- It will generate the following error
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

The way to make it work again, is by "finishing" your transaction. As you cannot commit it, you have to roll it back:

ROLLBACK;

this now works...

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('anotherid', 'Mike');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Your Node.js client connection framework might have an autocommit/autorollback feature that might automatically finish the job for you [don't know enough about Node.js DB clients to advise on them]. If not, you get out of the "aborted" state by just rolling back and finishing your transaction.