Horton

Thread

  1. Re: ON ERROR triggers

    Vadim Mikheev <vmikheev@sectorbase.com> — 2002-01-07T19:14:29Z

    > 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

  2. Re: ON ERROR triggers

    Don Baccus <dhogaza@pacifier.com> — 2002-01-07T19:52:27Z

    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

  3. Re: ON ERROR triggers

    Holger Krug <hkrug@rationalizer.com> — 2002-01-08T07:59:15Z

    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

  4. Re: ON ERROR triggers

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-01-08T09:06:42Z

    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).

  5. Re: ON ERROR triggers

    Holger Krug <hkrug@rationalizer.com> — 2002-01-08T09:21:13Z

    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