1

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?

  • 1
    Well, you have to close the transaction with a well-targeted ROLLBACK. This might help (not a NodeJS person myself): github.com/brianc/node-postgres/wiki/Transactions – dezso Dec 8 '16 at 15:09
  • Transaction is an indivisible operation. If something wrong goes inside the transaction then the whole transaction should be rolled back. So call rollback; in this case. – Abelisto Dec 8 '16 at 15:11
  • The code shown doesn't do what you say in the sense that the COMMIT, when sent in a failed transaction, actually executes a ROLLBACK and puts back the connection immediately in a state where it can accept new queries or transactions. – Daniel Vérité Dec 8 '16 at 16:05
4

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.

  • Thank you, this pointed me in the right direction, I now rollback on failure. I guess I didn't quite get how transactions worked as in many other cases the connection terminates meaning an explicit rollback is not needed. – FireLeopard Dec 13 '16 at 10:58

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.