Re: ON ERROR triggers

On Fri, Jan 04, 2002 at 01:56:51PM -0500, Jan Wieck wrote:
> Holger Krug wrote:
> > For an application I have to code I currently implement ON ERROR
> > TRIGGERS which shall be called after UNIQUE, CHECK, NOT NULL and REFERENCES
> > violations.
>
> 1. PostgreSQL doesn't know anything about ROLLBACK. It
> simply discards transaction ID's. Each row
> (oversimplified but sufficient here) has a transaction ID
> that created it and one for the Xact that destroyed it.
> By discarding an XID, rows that where created by it are
> ignored later, while rows destroyed by it survive.

I know this. "Marking a transaction for rollback" has the following
consequences:

CommitTransaction(void)
{
--snip--
/*
* check if the transaction is marked for rollback
*/
if (s->markedForRollback)
{
elog(DEBUG, "CommitTransaction: marked for rollback");
AbortTransaction();
CleanupTransaction();
return;
}
--snip--
}

> 2. When inserting a new row, first the data row in stored in
> the table, then (one by one) the index entries are built
> and stored in the indexes.

I know this executor code, too. The code is pretty good readable.

> Now you do an INSERT ... SELECT ...
>
> Anything goes well, still well, you work and work and at the
> 25th row the 3rd index reports DUPKEY. Since there are BEFORE
> INSERT triggers (I make this up, but that's allowed here), 3
> other tables received inserts and updates as well. BEFORE
> triggers are invoked before storage of the row, so the ones
> for this DUP row are executed by now already, the row is in
> the table and 2 out of 5 indexes are updated.
>
> Here now please explain to me in detail what exactly your ON
> ERROR UNIQUE trigger does, because with the ATOMIC
> requirement on statement level, I don't clearly see what it
> could do. Will it allow to break atomicity? Will it allow to
> treat this UNIQUE violation as, "yeah, such key is there, but
> this is different, really"?

It will do the following:

As a preparation I have to make some small changes of the interfaces
of AM index insertion methods, which allow to give information about
the error handler to the index insertion method. This done, after
detection of the DUPKEY constraint violation the code will execute
the following way:

1) Mark the transaction for rollback. As a consequence the transaction
will never commit, hence database integrity is assured in spite of
what follows. (See the code snippet above.)
2) Insert the DUPKEY into the index. This allows to collect some more
comprehensive error reports, what is the main purpose of my proposal.
3) Execute the error handler which, in most cases, will write an
error report into some TEMP table or do something similar.
4) Proceed with the 4th index and so on the normal way.

*Why* this should be done is explained in more detail in my answer to
Vadim's mail which I'm now going to write.

--
Holger Krug
hkrug(at)rationalizer(dot)com