how to continue a transaction after an error?

Hello,

I would like to insert a bunch of rows in a table in a transaction. Some of
the insertions will fail due to constraints violation. When this happens,
Postgres automatically ends the transaction and rolls back all the previous
inserts. I would like to continue the transaction and issue the
commit/rollback command myself.

How to do it?
Is there any setting I am missing?
Is it possible at all?

Thanks,
Cristi

On Mon, 13 Nov 2000, Cristi Petrescu-Prahova wrote:

> Hello,
>
> I would like to insert a bunch of rows in a table in a transaction. Some of
> the insertions will fail due to constraints violation. When this happens,
> Postgres automatically ends the transaction and rolls back all the previous
> inserts. I would like to continue the transaction and issue the
> commit/rollback command myself.
>
> How to do it?
> Is there any setting I am missing?
> Is it possible at all?

Currently, postgres treats all errors as critical ones that require a
complete rollback of transaction (although I believe it does not
immediately do the rollback, it should go into abort state). There's been
on and off talk about changing this, but nothing really has been decided i
believe.

On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote:
> Hello,
>
> I would like to insert a bunch of rows in a table in a transaction. Some of
> the insertions will fail due to constraints violation. When this happens,
> Postgres automatically ends the transaction and rolls back all the previous
> inserts. I would like to continue the transaction and issue the
> commit/rollback command myself.
>
> How to do it?
> Is there any setting I am missing?
> Is it possible at all?

Patient: "Doctor, it hurts when I bend my arm behind my back like
this. Can you help me?"
Doctor: "Sure, don't do that."

But seriously, this comes up from time to time. PostgreSQL is a little
stricter than most DBMSs with regards to transactional semantics, but
there are good reasons for this, involving tradeoffs of locking, MVCC,
"autocommit" mode, etc.

Let's look at transactions in general. When you start a transaction,
you're telling the backend "treat all of these statements as one, big,
all or nothing event." Just the thing for, say, balance transfers in
a bookkeeping application, but not something you need for storing web
log URL hits. If the backend isn't strict, how would you want it to
distinguish between "I really mean it, this time" and "that's o.k.,
go ahead anyway?"

If you want (need, if you're using large objects) transactions, you
really need to think about your transaction boundries. Don't just wrap
your whole frontend in one big, long lived transaction: close and reopen
your transaction for those inserts that are allowed to fail. Or don't do
them inside a multistatement transaction at all: let each one run in
it's own transaction space (i.e. other databases "autocommit" mode)

Ross
--
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers
and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.

I believe the known mechanism to cope with errors inside transactions are
savepoints. That doesn't seem to be the case, though.

If you don't care if some inserts fail, then you have multiple transactions
instead of just one (use autocommit).

If you want a transaction, check existence before each insert. That's the
way to do it.

> -----Original Message-----
> I would like to insert a bunch of rows in a table in a
> transaction. Some of
> the insertions will fail due to constraints violation. When this happens,
> Postgres automatically ends the transaction and rolls back all
> the previous
> inserts. I would like to continue the transaction and issue the
> commit/rollback command myself.

Multiple transactions carry a price: one cannot isolate the insertions from
other sessions. This is not desirable in my case.
The second suggestion is valuable.
Thanks,
Cristi

----- Original Message -----
From: Edmar Wiggers <edmar(at)brasmap(dot)com>
To: Cristi Petrescu-Prahova <cristipp(at)lasting(dot)ro>;
<pgsql-sql(at)postgresql(dot)org>
Sent: Monday, November 13, 2000 10:13 PM
Subject: RE: [SQL] how to continue a transaction after an error?

>
> I believe the known mechanism to cope with errors inside transactions are
> savepoints. That doesn't seem to be the case, though.
>
> If you don't care if some inserts fail, then you have multiple
transactions
> instead of just one (use autocommit).
>
> If you want a transaction, check existence before each insert. That's the
> way to do it.
>
> > -----Original Message-----
> > I would like to insert a bunch of rows in a table in a
> > transaction. Some of
> > the insertions will fail due to constraints violation. When this
happens,
> > Postgres automatically ends the transaction and rolls back all
> > the previous
> > inserts. I would like to continue the transaction and issue the
> > commit/rollback command myself.

At 14:08 13/11/00 -0600, Ross J. Reedstrom wrote:
>On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote:
>> Hello,
>>
>> I would like to insert a bunch of rows in a table in a transaction. Some of
>> the insertions will fail due to constraints violation. When this happens,
>> Postgres automatically ends the transaction and rolls back all the previous
>> inserts. I would like to continue the transaction and issue the
>> commit/rollback command myself.
>>
>> How to do it?
>> Is there any setting I am missing?
>> Is it possible at all?
>

Not possible; the error handling in PGSQL is a bit of a mess (not
necessarily a fault of PG), and it's not possible (currently) to rollback
single statements inside a larger transaction. This feature has to come,
but *not* AFAICT in the next release.

>But seriously, this comes up from time to time. PostgreSQL is a little
>stricter than most DBMSs with regards to transactional semantics, but
>there are good reasons for this, involving tradeoffs of locking, MVCC,
>"autocommit" mode, etc.

Not to mention the fact that we did not support sub-transactions. But I
think WAL does this for us, so we can hope for the feature RSN.

>When you start a transaction,
>you're telling the backend "treat all of these statements as one, big,
>all or nothing event."

This is actually contrary to the standard. Statements are atomic, and a
failed statement should not abort the TX:

The execution of all SQL-statements other than SQL-control
statements is atomic with respect to recovery. Such an
SQL-statement is called an atomic SQL-statement.

...

An SQL-transaction cannot be explicitly terminated within an
atomic execution context. If the execution of an atomic
SQL-statement is unsuccessful, then the changes to SQL-data or schemas
made by the SQL-statement are canceled.

>If you want (need, if you're using large objects) transactions, you
>really need to think about your transaction boundries. Don't just wrap
>your whole frontend in one big, long lived transaction

Totally agree; transactions will keep locks. Release them as soon as the
business rules and application design says that you can. Note that
commit-time constraints may make the commit fail; in this case PG will
force a rollback, but it *should* allow corrective action and another
attempt at a commit.

>close and reopen
>your transaction for those inserts that are allowed to fail.

This is very good advice for PGSQL, but bad advice for general DB
programming. At the end of the day, the database is the final arbiter of
valid data (through triggers, constraints etc that implement business
rules). Since we don't want to duplicate all of the rules from the database
within the application, we need to rely on the database telling us that the
last operation failed so that we can *choose* to rollback or choose to
change the processing.

A classic example would be processing batches of data - open a file, read a
line, insert it, if the data is bad, insert it into a list of exceptions,
keep reading file; this needs to be done in one TX since after commit we
delete the file, and we don't want partially loaded batches if the machine
crashes. I admit this example demonstrates my age, but this kind of
processing is still necessary.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

> >When you start a transaction,
> >you're telling the backend "treat all of these statements as one, big,
> >all or nothing event."
>
> This is actually contrary to the standard. Statements are atomic, and a
> failed statement should not abort the TX:
>
> The execution of all SQL-statements other than SQL-control
> statements is atomic with respect to recovery. Such an
> SQL-statement is called an atomic SQL-statement.
>
> ...
>
> An SQL-transaction cannot be explicitly terminated within an
> atomic execution context. If the execution of an atomic
> SQL-statement is unsuccessful, then the changes to SQL-data or schemas
> made by the SQL-statement are canceled.

This I agree with in general. You can almost defend the current behavior
by saying all errors cause an "unrecoverable error" (since I don't see a
definition of unreverable errors), but we're doing that wrong too since
that should initiate a rollback as opposed to our current behavior.
Admittedly, having an SQLSTATE style error code would help once we had
that so you could actually figure out what the error was.

> >If you want (need, if you're using large objects) transactions, you
> >really need to think about your transaction boundries. Don't just wrap
> >your whole frontend in one big, long lived transaction
>
> Totally agree; transactions will keep locks. Release them as soon as the
> business rules and application design says that you can. Note that
> commit-time constraints may make the commit fail; in this case PG will
> force a rollback, but it *should* allow corrective action and another
> attempt at a commit.

This I disagree with for commit time constraints unless stuff was changed
between the draft I have and final wording:
"When a <commit statement> is executed,
all constraints are effectively checked and, if any constraint
is not satisfied, then an exception condition is raised and the
transaction is terminated by an implicit <rollback statement>."

Other places they are a little less explicit about failed commits, but it
certainly allows a cancelation of changes:
"If an SQL-transaction is
terminated by a <rollback statement> or unsuccessful execution of
a <commit statement>, then all changes made to SQL-data or schemas
by that SQL-transaction are canceled. Committed changes cannot be
canceled. If execution of a <commit statement> is attempted, but
certain exception conditions are raised, it is unknown whether or
not the changes made to SQL-data or schemas by that
SQL-transaction are canceled or made persistent.

And I think this makes sense. If you're committing then you're saying
you're done and that you want the transaction to go away. If you just
want to check deferred constraints, there's set constraints mode. I could
almost see certain recoverable internal state things being worth not doing
a rollback for, but not constraints.

At 22:23 13/11/00 -0800, Stephan Szabo wrote:
>Admittedly, having an SQLSTATE style error code would help once we had
>that so you could actually figure out what the error was.

Yep, that would be nice.

>> Totally agree; transactions will keep locks. Release them as soon as the
>> business rules and application design says that you can. Note that
>> commit-time constraints may make the commit fail; in this case PG will
>> force a rollback, but it *should* allow corrective action and another
>> attempt at a commit.
>
>This I disagree with for commit time constraints unless stuff was changed
>between the draft I have and final wording:
> "When a <commit statement> is executed,
> all constraints are effectively checked and, if any constraint
> is not satisfied, then an exception condition is raised and the
> transaction is terminated by an implicit <rollback statement>."

Just checked the SQL99 stuff, and you are quite right - commit it terminal
no matter what.

>If you're committing then you're saying
>you're done and that you want the transaction to go away.

Not only that, but trying to unravel a constraint failure at commit-time
would (except in trivial cases) be almost impossible. Best thing is to
rollback.

>If you just
>want to check deferred constraints, there's set constraints mode.

True.

>I could
>almost see certain recoverable internal state things being worth not doing
>a rollback for, but not constraints.

Not true, eg, for FK constraints. The solution may be simple and the
application needs the option to fix it. Also, eg, the triggered data
*could* be useful in reporting the error (or fixing it in code), so an
implied rollback is less than ideal. Finally, custom 'CHECK' constraints
could be designed for exactly this purpose (I have done this in DBs before).

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Tue, 14 Nov 2000, Philip Warner wrote:

> >I could
> >almost see certain recoverable internal state things being worth not doing
> >a rollback for, but not constraints.
>
> Not true, eg, for FK constraints. The solution may be simple and the
> application needs the option to fix it. Also, eg, the triggered data
> *could* be useful in reporting the error (or fixing it in code), so an
> implied rollback is less than ideal. Finally, custom 'CHECK' constraints
> could be designed for exactly this purpose (I have done this in DBs before).

I was actually talking about commit time rollback there, not statement
time. I could theoretically see commit time non-rollback in cases of a
presumed transient internal state thing (now, I can't think of any in
practice, but...)

For a commit time check, I still think preceding with a set constraints
all immediate is better if you want to actually see if you're safe to
commit.