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@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 assured in 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 purpose of 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 for constraint
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 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.

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