errors on COMMIT and transaction states

From: Andrei Matei <andreimatei1(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: errors on COMMIT and transaction states
Date: 2017-09-22 21:11:31
Message-ID: CABWLsetreNyL2DmJ135KbVaT326_+e1EPJ0Ljy1yieBroWOjYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

I've got a question about the state in which a session/transaction finds
itself in case a COMMIT statement fails. I hope this is a good mailing list
to ask it on.
Is it true that a failed COMMIT behaves just like a ROLLBACK statement
would have if it was issues in its stead? In other words, is it true that
the transaction is rolled back (as it is on every error) and the session
will accept new statements without the need for the client to issue a
ROLLBACK?

And to extend the question a bit:
Is is true that, if a client uses the "simple query" flavor of the pgwire
protocol and sends multiple SQL commands in a single query string looking
like "BEGIN; <stmt 1>; <stmt 2>;...;COMMIT" then, when the result(s) come
and there was an error somewhere, the session can be in either of two
states:
- in an error state if the error was encountered by one of stmt1, stmt2,
etc. Meaning that future statements sent on future query strings will be
rejected with "ERROR: current transaction is aborted, commands ignored
until end of transaction block" and the client needs to send a ROLLBACK to
get itself out of this sticky situation?
- in a regular state, accepting new statements, if the error was
encountered by COMMIT.

If everything I've said before is true, is there any guidance / best
practices that developers and/or client drivers should implement for
dealing with this "should I send a ROLLBACK so I can continue to use my
connection or not?" question when receiving an error after sending a
multi-statement query string that contains a COMMIT in it?

Thank you very much,

- Andrei

Browse pgsql-general by date

  From Date Subject
Next Message legrand legrand 2017-09-22 21:51:24 Re: Multicolumn Index on OR conditions
Previous Message George Neuner 2017-09-22 15:25:50 Re: VM-Ware Backup of VM safe?