Re: failed Delete after Insert in a transaction

Stephan Szabo wrote:
> On Sun, 23 Jul 2000, Jan Wieck wrote:
>
> > Andrew Snow wrote:
> > >
> > > Why won't PostgreSQL let me do this?
> > >
> > > db=# begin;
> > > BEGIN
> > > db=# insert into foo (name) values ('hmmm');
> > > INSERT 22288 1
> > > db=# delete from foo where name='hmmm';
> > > ERROR: triggered data change violation on relation "foo"
> > > db=# abort;
> > >
> > > The table foo is defined like this:
> > >
> > > CREATE TABLE foo (
> > > ID serial PRIMARY KEY,
> > > Name text NOT NULL
> > > );
> > >
> > >
> > > I can't work out what I am doing wrong!
> > >
> > >
> > > Note, there is another table that REFERENCES this table, but as you can see
> > > in the example transaction above, I don't touch any other tables. Also note
> > > that it works fine outside of a transaction.
> >
> > It is according to the SQL3 specs, that you cannot modify one
> > and the same row, that is or might be subject to referential
> > integrity or triggers, multiple times in one transaction. If
> > you touch anything else or not doesn't matter.
>
> Seems like a fairly silly restriction in this case . I wonder why they
> added it? Oh well, ours is not to wonder why, right?
>
> Admittedly, the SQL92 triggered data change is also fairly dumb, since
> it seems to only be possible with real stupid setups (one column that
> is reference to multiple other things that themselves reference each
> other) or MATCH PARTIAL.

I think it has to do with the possible implementation
specific differences that arise from it. Have an ON UPDATE
SET NULL referential action, now you UPDATE a key and later
UPDATE it again to the old values. What should the constraint
do? Some implementation could state "the value hasn't changed
from the atomicity PoV". Another one is right too saying "it
has been touched in the Xact".

Jan

--

#======================================================================#
# 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 #