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,
thetransaction is marked for rollback (but not actually rolled back) and the error trigger is called (getting the
conflictingtuple 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 [
ONERROR 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
UPDATEaction ] [ ON ERROR trigger ( arguments ) ]
C) Most of the existing triggers would become error-prone, because the checks made at trigger start do not comprise
thenew possibilities to call a trigger as error handler. Hence if a trigger, which is conceived to be a e.g. BEFORE
INSERTtrigger 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
ERRORCHECK trigger.
Good luck for 7.2 !
--
Holger Krug
hkrug@rationalizer.com