Re: transaction processing after error in statement

Holger Jakobs wrote:

>>
>> Why is that "funny behaviour" for you? By putting the statements into
>> a transaction block you told the data management system "I want this
>> group of statements to be atomic". Atomic means all or nothing. It
>> might not be exactly what you intended to say, and you have a point
>> if you conclude that PostgreSQL is limited because it doesn't let you
>> say anything in between all or nothing. But after all, thus far it is
>> only doing what you asked for.
>>
>
> It is "funny behaviour", because I expect those operations of the
> transaction, which executed successfully, to be performed in an atomic
> way. It is obvious that I cannot expect an operation which reported an
> error to have any effect.
>
> "Atomic" means that all operations (whether successful or not) will be
> carried out all together or none of them - but only the successful ones
> will have had an effect.

As long as we talk in an SQL context, can you please stick to SQL terms?
I don't know exactly what you mean with "operation". If for example the
statement

DELETE FROM order_line WHERE ol_ordernum = 4711;

has 12 matching rows in order_line, is an operation the removal of one
single order line or do the actions performed by the triggers fired due
to their removal count as separate operations for you? And if there is
one that cannot be deleted because a row in another table with a foreign
key references it, do you delete none of them or the remaining 11? And
if you decide to delete none, how do you magically undo the work of the
BEFORE triggers if you hit the foreign key after successfully processing
5 rows? Is there an SQL return code for "partial success"?

The question about "partial success" is the important part here. Imagine
a stored procedure in PL/pgSQL consisting of two INSERTs. One fails with
a duplicate key error, the other one succeeds. The language lacks for
technical reasons an exception handling mechanism, so you have to define
if the other statement or nothing of the procedure succeeds, because you
have no chance to report "partial success", there is no return code
defined for that.

>
> Again: Why not make a difference between "commit" and "rollback" in a
> transaction in this case? Why not let the user decide which parts should
> be commited? The practical reason is that programming would become a lot
> more convenient. (if there is a practical reason it does not necessarily
> need a technical reason, I believe.)

Nobody said that it should not be possible. But you have to dig a little
deeper and make a bit more of a complete proposal for this, covering the
different possible failure reasons, definitions how exactly to react in
case of statements affecting multiple rows, related triggers and so on
and so forth. "Make a difference between commit and rollback" is way too
fuzzy here.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #