Thread: failed Delete after Insert in a transaction
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.
I am running postgresql 7.02 on FreeBSD 3.4-STABLE.
Thanks
Andrew
> 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.
>
> I am running postgresql 7.02 on FreeBSD 3.4-STABLE.
>
>
> Thanks
>
>
> Andrew
You should commit transaction before changing any foreign key that was
affected by this transaction.
So if you really want to delete the row you have just inserted (I wonder why
:) you should write your own FOREIGN KEY triggers. It implies performance
loss, but allows you to do everything you want over the referenced fields.
Alex Bolenok.
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.
>
> I am running postgresql 7.02 on FreeBSD 3.4-STABLE.
>
>
> Thanks
>
>
> Andrew
>
>
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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.
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 #