Re: transaction processing after error in statement

On 11 Nov, Jan Wieck wrote:
> 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"?
OK, let's say "statement" instead of "operation".

No, there is no partial success. Either a statement delivers an "OK" or
it doesn't. Actually, you will have to undo anything the statement did
before the first error occurs. This may mean that you need some kind of
savepoint. If so, the necessity to implent this shows and should be
given a high priority. I don't know how the other RDMBS do it, but they
do. I am talking from the perspective of an RDBMS user, not as an
implementor.

Calling a procedure is a statement as well, and it includes all other
procedures called from this procedure. So the statement level is always
the statements that were carried out directly in the transaction. If
anything within one statement fails, the statement was not carried out
and must not have any effect. It is not important whether the procedure
was fired by a trigger or called by another procedure.

Are there any Open Source RDBMS which behave like Oracle, Access and
Allbase? If so, one might look into their code to find out how they have
implented it.

Coming back to Standards, here is a quote from the ANSI document:

4.28 SQL-transactions

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects unrecoverable errors. When
such an error occurs, an exception condition is raised: transaction
rollback with an implementation-defined subclass code.

This means that a rollback does not have to be initiated if an
unrecoverable error occurs, it only _may_ happen. Since it is
impractical, it should not.

AN EXAMPLE:

Let's have two tables, employees and doctors in a hospital.

create table emp (
empno integer primary key,
name varchar(40)
);

create table doctor (
empno integer primary key references emp,
beepernumber integer unique
);

Now let a user enter the data of a doctor. First the data of the
employee part are sent to the database:
insert into emp values (1, 'Fred');
--> success

Second the doctor-special data are sent to the database:
insert into doctor values (1, 34);
-->error, beepernumber already present, unique key violation

Since there was an error, we let the user key in a different
beeper number for the doctor and send the data to the
database:
insert into doctor (1, 45);
-->should be successful (if 45 is not already there) and it _is_ in
Allbase, Oracle, Access
-->Postgres tells you something about and *ABORT* state

We commit the transaction:
commit work;

Effect in all other databases: a successfully entered doctor
Effect in PostgreSQL: nothing!

To get the same effect in PostgreSQL, we would have to rollback
(or commit, no difference) the transaction after the attempt of
inserting the non-unique beeper number and then re-insert all
data into all tables. WHY? It only makes programming more
complex, thus error-prone.

I would appreciate if most of the difficult tasks could be done within
the database system - that's what it's there fore. The first reason is
that the implementors usually are far more advanced programmers than the
programmers of applications. The second reason is that this isolates the
tricky programming from the already complex logic of the application.

Additionally, whether one might personally think that this behaviour is
important or not, it is what most commercial RDBMS do. So it would make
porting of applications from e. g. Oracle to PostgreSQL dramatically
easier. Until now, this has been one of the pitfalls. That's why I
brought this up in the first place.

Sincerely,

Holger

--
Holger(at)Jakobs(dot)com, Bergisch Gladbach, Germany
Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66