PD: triggered data change violation on relation "tbl_b"

You cannot insert and delete the same data within one transaction.
Only one change of a row is allowed.

Greetings
Andreas

On Wed, 29 Aug 2001 13:18:02 +0200
"Pawel Pawlowski" <pawel.pawlowski@breitenbach.pl> wrote:

When I insert to table new row and after this in the same transaction I delete this row I get such error:
triggered data change violation on relation "tbl_b"

I've created database using simple script:

CREATE TABLE tbl_a
(
pn_id SERIAL,
pn_a VARCHAR(400) NOT NULL,
PRIMARY KEY (pn_id)
);
CREATE TABLE tbl_b
(
pc_id INT4 NOT NULL REFERENCES tbl_a (pn_id) ON UPDATE CASCADE ON DELETE CASCADE,
pc_b VARCHAR(40) NOT NULL,
PRIMARY KEY (pc_id, pc_b)
);
INSERT INTO tbl_a VALUES (1, 'xxx');

And this is the sample script that I use to generete this bug:

begin transaction;
insert into tbl_b values (1, 'xxx');
delete from tbl_b where pc_id=1;
ERROR: triggered data change violation on relation "tbl_b"

How to solve this problem ?????

------------------------------
Andreas Wernitznig
Insilico Software GmbH
E-Mail: andreas@insilico.com
Web: www.insilico.com
------------------------------

Im changing now database from Interbase 6.0 to PosgreSql 7.1.2. With IB
there is no problem to do such things.
This problem only exist when I create table tbl_a with references
(REFERENCES tbl_a (pn_id) ON UPDATE CASCADE ON DELETE CASCADE). Without this
part everything works OK. So I think that problem is with triger.

----- Wiadomosc oryginalna -----
Od: "Andreas Wernitznig" <andreas@insilico.com>
Do: "Pawel Pawlowski" <pawel.pawlowski@breitenbach.pl>
DW: <pgsql-bugs@postgresql.org>
Wyslano: 29 sierpnia 2001 13:20
Temat: Re: [BUGS] PD: triggered data change violation on relation "tbl_b"

You cannot insert and delete the same data within one transaction.
Only one change of a row is allowed.

Greetings
Andreas

On Wed, 29 Aug 2001 13:18:02 +0200
"Pawel Pawlowski" <pawel.pawlowski@breitenbach.pl> wrote:

When I insert to table new row and after this in the same transaction I

delete this row I get such error:

triggered data change violation on relation "tbl_b"

I've created database using simple script:

CREATE TABLE tbl_a
(
pn_id SERIAL,
pn_a VARCHAR(400) NOT NULL,
PRIMARY KEY (pn_id)
);
CREATE TABLE tbl_b
(
pc_id INT4 NOT NULL REFERENCES tbl_a (pn_id) ON UPDATE CASCADE ON

DELETE CASCADE,

This was a mistake in the interpretation of the spec (modification of
the same key row referenced by a foreign key constraint in the same
statement more than once is an error is how we believe the spec meant
it, but there's a case where they mention transaction and it got
misinterpreted). I don't think anyone's permanently fixed it yet, but
making the check disappear involves commenting out the two blocks that
throw the message in backend/commands/trigger.c.

On Wed, 29 Aug 2001, Pawel Pawlowski wrote:

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

This was a mistake in the interpretation of the spec (modification of
the same key row referenced by a foreign key constraint in the same
statement more than once is an error is how we believe the spec meant
it, but there's a case where they mention transaction and it got
misinterpreted). I don't think anyone's permanently fixed it yet, but
making the check disappear involves commenting out the two blocks that
throw the message in backend/commands/trigger.c.

Would it be better to just do that until a proper solution is
implemented? What is the downside of not making any check?

regards, tom lane

On Mon, 3 Sep 2001, Tom Lane wrote:

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

This was a mistake in the interpretation of the spec (modification of
the same key row referenced by a foreign key constraint in the same
statement more than once is an error is how we believe the spec meant
it, but there's a case where they mention transaction and it got
misinterpreted). I don't think anyone's permanently fixed it yet, but
making the check disappear involves commenting out the two blocks that
throw the message in backend/commands/trigger.c.

Would it be better to just do that until a proper solution is
implemented? What is the downside of not making any check?

I believe the intention of the check was to prevent a case from
occurring where you update a value and then have a cascade constraint
change it for a second time if you have some kind of recursive
constraint structure. I think this could occur if you had something
like TableA.id references TableB.id cascade and then TableB.id
references TableA.id set default, then when you changed tableb.id,
the tablea.id would change which would cause the tableb.id to be
set to the default (but should error I think).

This is probably a smaller failure case however, given it took
some time for me to come up with a good failure, and I think more
people are hitting the current problem, so I'd vote for changing
it.