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
Views: Raw Message | Whole Thread | Download 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


Browse pgsql-hackers by date

  From Date Subject
Next Message Doug McNaught 2002-01-04 18:58:09 Re: Updated TODO item
Previous Message Bruce Momjian 2002-01-04 18:43:41 Re: Updated TODO item