Re: ON ERROR triggers - Mailing list pgsql-hackers

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
assuredin 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
purposeof 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@rationalizer.com