Обсуждение: PD: triggered data change violation on relation "tbl_b"
When I insert to table new row and after this in the same transaction I del= ete this row I get such error:=20 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 DE= LETE CASCADE, pc_b VARCHAR(40) NOT NULL, PRIMARY KEY (pc_id, pc_b)=20=20 ); 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=3D1; ERROR: triggered data change violation on relation "tbl_b" How to solve this problem ?????
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, > > 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 > ------------------------------ >
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: > 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.
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.