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.

The implementation plan is as follows:

1) Make `CurrentTransactionState' static in `xact.c' (done, could
   be posted for 7.2, because this could be seen as a bug)
2) Allow a transaction to be marked for rollback, in which case
   it proceeds but rolls back at commit time. It is not possible
   to remove the mark, hence database integrity is assured. (done)
3) Add an ON ERROR UNIQUE trigger OID to pg_index. If the uniqueness
   constraint is violated and such a trigger exists, the transaction is
   marked for rollback (but not actually rolled back) and the error
   trigger is called (getting the conflicting tuple as OLD and the
   tuple to be inserted as NEW). (what I'm currently doing)
4) Add ON ERROR CHECK, ON ERROR NOT NULL and ON ERROR REFERENCES triggers
   in a similar way. (to do)

This supersedes what I discussed some days ago with Tom Lane on this list.

My questions are:

A) Are the hackers interested to integrate those changes, if reasonable
   coded, into the PostgreSQL sources, e.g. for 7.3 ?
B) What are the hackers' proposals for the syntax at the query string level.
   I think about something like:
   UNIQUE [ ON ERROR trigger ( arguments ) ]
   CHECK ( expression ) [ ON ERROR trigger ( arguments ) ]
   NOT NULL [ ON ERROR trigger ( arguments ) ]
   REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ]
    [ ON ERROR trigger ( arguments ) ]
C) Most of the existing triggers would become error-prone, because the
   checks made at trigger start do not comprise the new possibilities to
   call a trigger as error handler. Hence if a trigger, which is
   conceived to be a e.g. BEFORE INSERT trigger is used as a e.g.
   ON ERROR CHECK trigger, it would not get informed about this. The
   results would be unpredictable.
   Is this seen to be a problem ?
   Don't forget: Nobody is forced to use a BEFORE INSERT trigger as a
   ON ERROR CHECK trigger.

Good luck for 7.2 !

-- 
Holger Krug
hkrug@rationalizer.com

Fri, 4 Jan 2002 17:36:57 +0100
Holger Krug <hkrug@rationalizer.com>
ON ERROR triggers
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.
>
> The implementation plan is as follows:
>
> 1) Make `CurrentTransactionState' static in `xact.c' (done, could
>    be posted for 7.2, because this could be seen as a bug)
> 2) Allow a transaction to be marked for rollback, in which case
>    it proceeds but rolls back at commit time. It is not possible
>    to remove the mark, hence database integrity is assured. (done)
> 3) Add an ON ERROR UNIQUE trigger OID to pg_index. If the uniqueness
>    constraint is violated and such a trigger exists, the transaction is
>    marked for rollback (but not actually rolled back) and the error
>    trigger is called (getting the conflicting tuple as OLD and the
>    tuple to be inserted as NEW). (what I'm currently doing)
> 4) Add ON ERROR CHECK, ON ERROR NOT NULL and ON ERROR REFERENCES triggers
>    in a similar way. (to do)

    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.

    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.

    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"?

    What am I missing here?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Fri, 4 Jan 2002 13:56:51 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
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@rationalizer.com

Mon, 7 Jan 2002 08:31:58 +0100
Holger Krug <hkrug@rationalizer.com>
Re: ON ERROR triggers