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, 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