Re: failed Delete after Insert in a transaction

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.