Thread

Re: [HACKERS] TRANSACTIONS

sszabo@bigpanda.com — 2000-02-23T14:32:10Z

>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, if it 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 <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>.

4.28 SQL Transactions
	An SQL-transaction
         is terminated by a <commit statement> or a <rollback statement>.
         If an SQL-transaction is terminated by successful execution of a
         <commit statement>, then all changes made to SQL-data or schemas by
         that SQL-transaction are made persistent and accessible to all con-
         current and subsequent SQL-transactions. 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.

10.6 <constraint name definition> and <constraint attributes>
         4) When a constraint is effectively checked, if the constraint 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 is not
            set to integrity constraint violation, but is set to transaction
            rollback-integrity constraint violation (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 by the current SQL-transaction
              are canceled and an exception condition is raised: transac-
              tion rollback-integrity constraint violation.

            b) If any other error preventing commitment of the SQL-
              transaction has occurred, then any changes to SQL-data or
              schemas that were made by the current SQL-transaction are
              canceled and an exception condition 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 are made 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