Re: 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(at)Yahoo(dot)com #

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