Re: ON ERROR triggers - Mailing list 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)
> 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
destroyedby it survive.
 
   2.  When inserting a new row, first the data row in stored in       the  table, then (one by one) the index entries
arebuilt       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
areBEFORE   INSERT triggers (I make this up, but that's allowed here),  3   other  tables  received  inserts  and
updatesas well. BEFORE   triggers are invoked before storage of the row, so  the  ones   for  this  DUP row are
executedby 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