Re: [HACKERS] TRANSACTIONS

>At 11:32 AM 2/22/00 -0500, Tom Lane wrote:
>
>>I see no way that allowing the transaction to commit after an overflow
>>can be called consistent with the spec.
>
>You are absolutely right.  The whole point is that either a) everything
>commits or b) nothing commits.
>
>Having some kinds of exceptions allow a partial commit while other
>exceptions rollback the transaction seems like a very error-prone
>programming environment to me.

I'm not sure what Date says about this, but reading the spec I see
where the other way of looking at the commit is...  I'm sure I
missed something, but here's the relevant parts from a draft that I see:

4.10.1 Checking of constraints        When a constraint is checked other than at the end of an SQL-        transaction,
ifit is not satisfied, then an exception condition        is raised and the SQL-statement that caused the constraint to
be       checked has no effect other than entering the exception information        into the diagnostics area. When a
<commitstatement> 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>.

4.28 SQL TransactionsAn SQL-transaction        is terminated by a <commit statement> or a <rollback statement>.
Ifan SQL-transaction is terminated by successful execution of a        <commit statement>, then all changes made to
SQL-dataor schemas by        that SQL-transaction are made persistent and accessible to all con-        current and
subsequentSQL-transactions. If an SQL-transaction is        terminated by a <rollback statement> or unsuccessful
executionof        a <commit statement>, then all changes made to SQL-data or schemas        by that SQL-transaction
arecanceled. Committed changes cannot be        canceled. If execution of a <commit statement> is attempted, but
certainexception conditions are raised, it is unknown whether or        not the changes made to SQL-data or schemas by
thatSQL-transaction        are canceled or made persistent.
 

10.6 <constraint name definition> and <constraint attributes>        4) When a constraint is effectively checked, if
theconstraint is           not satisfied, then an exception condition is raised: integrity           constraint
violation.If this exception condition is raised as a           result of executing a <commit statement>, then SQLSTATE
isnot           set to integrity constraint violation, but is set to transaction           rollback-integrity
constraintviolation (see the General Rules           of Subclause 14.3, "<commit statement>").
 

14.3 <commit statement>        5) Case:
           a) If any constraint is not satisfied, then any changes to SQL-             data or schemas that were made
bythe current SQL-transaction             are canceled and an exception condition is raised: transac-             tion
rollback-integrityconstraint violation.
 
           b) If any other error preventing commitment of the SQL-             transaction has occurred, then any
changesto SQL-data or             schemas that were made by the current SQL-transaction are             canceled and an
exceptioncondition is raised: transaction             rollback with an implementation-defined subclass value.
 
           c) Otherwise, any changes to SQL-data or schemas that were made             by the current SQL-transaction
aremade accessible to all             concurrent and subsequent SQL-transactions.
 

---> Although I think that the current postgresql behavior is *better* than
the behavior as shown by the other databases, I think a case could be
made that 14.3 General Rule 5.a refers only to exceptions thrown by the
commit statement itself (any constraints that are checked at that time)
given the section of 4.10.1 and 10.6.  This wouldn't be inconsistant
by type of exception, but would mean that immediate constraints and
deferred ones play by different rules for determining how a commit 
works.
 I'm not entirely sure I like that behavior though.  It makes the
database less responsible for being in a reasonable state.  For example,
if you've got a parent and two children, but one of the children fails
due to say an overflow exception, you really want to roll it all back,
but the database won't do that unless the overflow is checked
at commit time (ugh!?!).

Stephan