Re: failed Delete after Insert in a transaction - Mailing list pgsql-general

From JanWieck@t-online.de (Jan Wieck)
Subject Re: failed Delete after Insert in a transaction
Date
Msg-id 200007231926.VAA12510@hot.jw.home
Whole thread Raw
In response to Re: failed Delete after Insert in a transaction  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-general
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@Yahoo.com #



pgsql-general by date:

Previous
From: Bill Barnes
Date:
Subject: RE: Migrating from Sybase
Next
From: wil van der wee
Date:
Subject: Connecting to PostgreSQL databases