Re: transaction processing after error in statement - Mailing list pgsql-sql

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@Yahoo.com #