Skip site navigation (1) Skip section navigation (2)

Re: ON ERROR triggers

From: Jan Wieck <janwieck(at)yahoo(dot)com>
To: Holger Krug <hkrug(at)rationalizer(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ON ERROR triggers
Date: 2002-01-04 18:56:51
Message-ID: 200201041856.g04IupN18191@saturn.jw.home (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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)
>    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?



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

Do You Yahoo!?
Get your free address at

In response to


pgsql-hackers by date

Next:From: Doug McNaughtDate: 2002-01-04 18:58:09
Subject: Re: Updated TODO item
Previous:From: Bruce MomjianDate: 2002-01-04 18:43:41
Subject: Re: Updated TODO item

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group