Thread: ON ERROR triggers
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
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
> 2) Allow a transaction to be marked for rollback, in which case > it proceeds but rolls back at commit time. It is not possible Sorry, can you explain one more time what's the point to continue make changes in transaction which will be rolled back? How about savepoints? Vadim
On Fri, Jan 04, 2002 at 01:56:51PM -0500, Jan Wieck wrote:
> 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.
>
> 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.
I know this. "Marking a transaction for rollback" has the following
consequences:
CommitTransaction(void)
{
--snip--/* * check if the transaction is marked for rollback */if (s->markedForRollback){ elog(DEBUG,
"CommitTransaction:marked for rollback"); AbortTransaction(); CleanupTransaction(); return;}
--snip--
}
> 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.
I know this executor code, too. The code is pretty good readable.
> 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"?
It will do the following:
As a preparation I have to make some small changes of the interfaces
of AM index insertion methods, which allow to give information about
the error handler to the index insertion method. This done, after
detection of the DUPKEY constraint violation the code will execute
the following way:
1) Mark the transaction for rollback. As a consequence the transaction will never commit, hence database integrity is
assuredin spite of what follows. (See the code snippet above.)
2) Insert the DUPKEY into the index. This allows to collect some more comprehensive error reports, what is the main
purposeof my proposal.
3) Execute the error handler which, in most cases, will write an error report into some TEMP table or do something
similar.
4) Proceed with the 4th index and so on the normal way.
*Why* this should be done is explained in more detail in my answer to
Vadim's mail which I'm now going to write.
--
Holger Krug
hkrug@rationalizer.com
On Fri, Jan 04, 2002 at 11:48:26AM -0800, Mikheev, Vadim wrote: > > 2) Allow a transaction to be marked for rollback, in which case > > it proceeds but rolls back at commit time. It is not possible > > Sorry, can you explain one more time what's the point to continue > make changes in transaction which will be rolled back? I think, I can. The point is to collect comprehensive error reports, mainly about failed modifications of complex structured data which is created/modified concurrently by several workers in an optimistic locking fashion. Because the data is so complex it won't help anybody if you print out a message as "index xy violated by tuple ab". Hence I want to collect all the errors to give the application/the user the possibility to make an overall assessment about what has to be done to avoid the error. This is also the reason, why I will insert a DUPKEY into an index after having marked the transaction for rollback (see my answer to Jan's mail). I deem this will give more informative error reports. I simply execute all, what the user wants to be done, and inform the user about all the errors occurring, not only the first one. Imagine CVS would inform you only about 1 conflict each time you asks to be informed about potential conflicts. Wouldn't it be annoying ? For sure, it would. Now think about databases. > How about savepoints? This would be my question to you: How about savepoints ? Do they help to achieve what I want to achieve ? -- Holger Krug hkrug@rationalizer.com
> The point is to collect comprehensive error reports, mainly about > failed modifications of complex structured data which is > created/modified concurrently by several workers in an optimistic > locking fashion. Because the data is so complex it won't help anybody > if you print out a message as "index xy violated by tuple ab". Hence I > want to collect all the errors to give the application/the user the > possibility to make an overall assessment about what has to be done to > avoid the error. ... > > How about savepoints? > > This would be my question to you: How about savepoints ? > Do they help to achieve what I want to achieve ? Ok, thanks. Yes, savepoints would not allow you to get comprehensive error reports in all cases (when you need to insert record with duplicate key to avoid errors caused by absence of such record etc). Though savepoints allow application to fix an error immediately after this error encountered (without wasting time/resources) I will not argue with you about how much such comprehensive reports are useful. I'd rather ask another question -:) How about constraints in DEFERRED mode? Looks like deferred mode allows you to do everything you need - ie make ALL required changes and then check everything when mode changed to immediate. Also note that this would be more flexible then trigger approach - you can change mode of individual constraint. Two glitches though: 1. I believe that currently transaction will be aborted on first error encountered, without checking all other changes forconstraint violations. I suppose this can be easily changed for your needs. And user would just point out what behaviour is required. 2. Not sure about CHECK constraints but Uniq/PrimaryKey ones are not deferrable currently -:( And this is muuuuuch worsedrawback then absence of comprehensive reports. It's more complex thing to do than on error triggers but someday itwill be implemented because of this is "must have" kind of things. Vadim
Mikheev, Vadim wrote: > 2. Not sure about CHECK constraints but Uniq/PrimaryKey ones are not > deferrable currently -:( And this is muuuuuch worse drawback then absence > of comprehensive reports. It's more complex thing to do than on error > triggers but someday it will be implemented because of this is "must > have" > kind of things. At some point they need to be deferred to statement end so update t set foo = foo + 1; works ... -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org
On Mon, Jan 07, 2002 at 11:14:29AM -0800, Mikheev, Vadim wrote: > I'd rather ask another question -:) How about constraints in DEFERRED mode? > Looks like deferred mode allows you to do everything you need - ie make ALL > required changes and then check everything when mode changed to immediate. > Also note that this would be more flexible then trigger approach - you can > change mode of individual constraint. > > Two glitches though: > 1. I believe that currently transaction will be aborted on first error > encountered, without checking all other changes for constraint > violations. That's the problem. > I suppose this can be easily changed for your needs. And user would just > point out what behaviour is required. I suppose changing this is what i'm doing with my proposed error handlers ;-) For error reporting there is no difference between DEFERRED and IMMEDIATE. The only advantage DEFERRED provides and for what it what added to the SQL standard is some pseudo-errors do not arise. > 2. Not sure about CHECK constraints but Uniq/PrimaryKey ones are not > deferrable currently -:( And this is muuuuuch worse drawback then absence > of comprehensive reports. It's more complex thing to do than on error > triggers but someday it will be implemented because of this is "must > have" > kind of things. A simple implementation of deferred UNIQUE constraints could be very easily provided bases on my error handlers. Imagine a deferred UNIQUE index where a DUPKEY is up to be inserted. When the DUPKEY appears in DEFERRED mode my error handler will: 1) not mark the transaction for rollback 2) add a trigger to the deferred trigger queue to do checks on the DUPKEY in the given index 3) that's all Maybe not the most efficient way, but a very clean implementation based on error handlers. Maybe now a little bit convinced of error handlers ? Would be glad. -- Holger Krug hkrug@rationalizer.com
On Tue, 8 Jan 2002, Holger Krug wrote: > > 2. Not sure about CHECK constraints but Uniq/PrimaryKey ones are not > > deferrable currently -:( And this is muuuuuch worse drawback then absence > > of comprehensive reports. It's more complex thing to do than on error > > triggers but someday it will be implemented because of this is "must > > have" > > kind of things. > > A simple implementation of deferred UNIQUE constraints could be very > easily provided bases on my error handlers. Imagine a deferred UNIQUE > index where a DUPKEY is up to be inserted. When the DUPKEY appears in > DEFERRED mode my error handler will: > > 1) not mark the transaction for rollback > 2) add a trigger to the deferred trigger queue to do checks on the DUPKEY > in the given index > 3) that's all ISTM that the above seems to imply that you could make unique constraints that don't actually necessarily constrain to uniqueness (an error handler that say didn't mark for rollback and did nothing to enforce it later, or only enforced it in some cases, etc...). If so, I'd say that any unique constraint that had an error condition for example couldn't be used as if it guaranteed uniqueness (for example as targets of fk constraints).
On Tue, Jan 08, 2002 at 01:06:42AM -0800, Stephan Szabo wrote: > On Tue, 8 Jan 2002, Holger Krug wrote: > > A simple implementation of deferred UNIQUE constraints could be very > > easily provided bases on my error handlers. Imagine a deferred UNIQUE > > index where a DUPKEY is up to be inserted. When the DUPKEY appears in > > DEFERRED mode my error handler will: > > > > 1) not mark the transaction for rollback > > 2) add a trigger to the deferred trigger queue to do checks on the DUPKEY > > in the given index > > 3) that's all > > ISTM that the above seems to imply that you could make unique > constraints that don't actually necessarily constrain to uniqueness (an > error handler that say didn't mark for rollback and did nothing to > enforce it later, or only enforced it in some cases, etc...). If so, > I'd say that any unique constraint that had an error condition for example > couldn't be used as if it guaranteed uniqueness (for example as targets > of fk constraints). What I said above was an extension of my original proposal, which consists of: 1) marking the transaction for rollback 2) ... I only wanted to show, that the addition I'm going to make to PostgreSQL, could be used to implemented DEFERRED UNIQUE constraints in a very simple way. Of course, this special error handler for DEFERRED UNIQUE constraints, which puts a trigger with the DUPKEY into that deferred trigger queue, could not be up-to the user but must be system-enforced. But - you're right. My previous mail didn't express this explicitely, hence your notice is correct. Thank you ! -- Holger Krug hkrug@rationalizer.com