Thread: Bug in FOREIGN KEY
This problem with foreign keys has been reported to me, and I have confirmed
the bug exists in current sources. The DELETE should succeed:
---------------------------------------------------------------------------
CREATE TABLE primarytest2 ( col1 INTEGER, col2 INTEGER,
PRIMARY KEY(col1, col2) );
CREATE TABLE foreigntest2 (col3 INTEGER, col4 INTEGER, FOREIGN KEY
(col3,col4) REFERENCES primarytest2 );
test=> BEGIN;
BEGIN
test=> INSERT INTO primarytest2 VALUES (5,5);
INSERT 27618 1
test=> DELETE FROM primarytest2 WHERE col1 = 5 AND col2 = 5;
ERROR: triggered data change violation on relation "primarytest2"
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian writes: > ERROR: triggered data change violation on relation "primarytest2" We're getting this report about once every 48 hours, which would make it a FAQ. (hint, hint) -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> Bruce Momjian writes: > > > ERROR: triggered data change violation on relation "primarytest2" > > We're getting this report about once every 48 hours, which would make it a > FAQ. (hint, hint) > First time I heard of it. Does anyone know more details? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote:
> > Bruce Momjian writes:
> >
> > > ERROR: triggered data change violation on relation "primarytest2"
> >
> > We're getting this report about once every 48 hours, which would make it a
> > FAQ. (hint, hint)
> >
>
>
> First time I heard of it. Does anyone know more details?
Think I misinterpreted the SQL3 specs WR to this detail. The checks must be made per statement, not at the
transaction level. I'll try to fix it, but we need to define what will happen with referential actions in the
case of conflicting actions on the same key - there are some possible conflicts:
1. DEFERRED ON DELETE NO ACTION or RESTRICT
Do the referencing rows reference to the new PK row with the same key now, or is this still a
constraint violation? I would say it's not, because the constraint condition is satisfied at the end of
thetransaction. How do other databases behave?
2. DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT
Again I'd say that the action should be suppressed because a matching PK row is present at
transactionend - it's not the same old row, but the constraint itself is still satisfied.
Implementing it that way (if it is correct that way) requires that the RI-triggers check that the key in
questionreally disappeared from the PK table, at least for the deferred invocation at transaction end. This
lookupis not required in the immediate case, so it would be possible to retain the current performance here,
but we'd need a mechanism that tells the trigger if it is actually invoked in immediate or deferred mode. Don't
knowhow to do that right now.
To fix it now, I'd tend to remove the triggered data change check in the trigger queue (where the error is
coming from) and add the extra PK lookup to the triggers for 7.1. Then think about the suppress of it with
an immediate/deferred flag mechanism for 7.2.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Can someone tell me where we are on this? > This problem with foreign keys has been reported to me, and I have confirmed > the bug exists in current sources. The DELETE should succeed: > > --------------------------------------------------------------------------- > > CREATE TABLE primarytest2 ( > col1 INTEGER, > col2 INTEGER, > PRIMARY KEY(col1, col2) > ); > > CREATE TABLE foreigntest2 (col3 INTEGER, > col4 INTEGER, > FOREIGN KEY (col3, col4) REFERENCES primarytest2 > ); > test=> BEGIN; > BEGIN > test=> INSERT INTO primarytest2 VALUES (5,5); > INSERT 27618 1 > test=> DELETE FROM primarytest2 WHERE col1 = 5 AND col2 = 5; > ERROR: triggered data change violation on relation "primarytest2" > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
This is Jan's reply to the issue. > Bruce Momjian wrote: > > > Bruce Momjian writes: > > > > > > > ERROR: triggered data change violation on relation "primarytest2" > > > > > > We're getting this report about once every 48 hours, which would make it a > > > FAQ. (hint, hint) > > > > > > > > > First time I heard of it. Does anyone know more details? > > Think I misinterpreted the SQL3 specs WR to this detail. The > checks must be made per statement, not at the transaction > level. I'll try to fix it, but we need to define what will > happen with referential actions in the case of conflicting > actions on the same key - there are some possible conflicts: > > 1. DEFERRED ON DELETE NO ACTION or RESTRICT > > Do the referencing rows reference to the new PK row with > the same key now, or is this still a constraint > violation? I would say it's not, because the constraint > condition is satisfied at the end of the transaction. How > do other databases behave? > > 2. DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT > > Again I'd say that the action should be suppressed > because a matching PK row is present at transaction end - > it's not the same old row, but the constraint itself is > still satisfied. > > Implementing it that way (if it is correct that way) requires > that the RI-triggers check that the key in question really > disappeared from the PK table, at least for the deferred > invocation at transaction end. This lookup is not required in > the immediate case, so it would be possible to retain the > current performance here, but we'd need a mechanism that > tells the trigger if it is actually invoked in immediate or > deferred mode. Don't know how to do that right now. > > To fix it now, I'd tend to remove the triggered data change > check in the trigger queue (where the error is coming from) > and add the extra PK lookup to the triggers for 7.1. Then > think about the suppress of it with an immediate/deferred > flag mechanism for 7.2. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
hi, there! On Mon, 22 Jan 2001, Bruce Momjian wrote: > > > This problem with foreign keys has been reported to me, and I have confirmed > > the bug exists in current sources. The DELETE should succeed: > > > > --------------------------------------------------------------------------- > > > > CREATE TABLE primarytest2 ( > > col1 INTEGER, > > col2 INTEGER, > > PRIMARY KEY(col1, col2) > > ); > > > > CREATE TABLE foreigntest2 (col3 INTEGER, > > col4 INTEGER, > > FOREIGN KEY (col3, col4) REFERENCES primarytest2 > > ); > > test=> BEGIN; > > BEGIN > > test=> INSERT INTO primarytest2 VALUES (5,5); > > INSERT 27618 1 > > test=> DELETE FROM primarytest2 WHERE col1 = 5 AND col2 = 5; > > ERROR: triggered data change violation on relation "primarytest2" I have another (slightly different) example: --- cut here --- test=> CREATE TABLE pr(obj_id int PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pr_pkey' for table 'pr' CREATE test=> CREATE TABLE fr(obj_id int REFERENCES pr ON DELETE CASCADE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE test=> BEGIN; BEGIN test=> INSERT INTO pr (obj_id) VALUES (1); INSERT 200539 1 test=> INSERT INTO fr (obj_id) SELECT obj_id FROM pr; INSERT 200540 1 test=> DELETE FROM fr; ERROR: triggered data change violation on relation "fr" test=> --- cut here --- we are running postgresql 7.1 beta3 /fjoe
> > Think I misinterpreted the SQL3 specs WR to this detail. The > > checks must be made per statement, not at the transaction > > level. I'll try to fix it, but we need to define what will > > happen with referential actions in the case of conflicting > > actions on the same key - there are some possible conflicts: > > > > 1. DEFERRED ON DELETE NO ACTION or RESTRICT > > > > Do the referencing rows reference to the new PK row with > > the same key now, or is this still a constraint > > violation? I would say it's not, because the constraint > > condition is satisfied at the end of the transaction. How > > do other databases behave? > > > > 2. DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT > > > > Again I'd say that the action should be suppressed > > because a matching PK row is present at transaction end - > > it's not the same old row, but the constraint itself is > > still satisfied. I'm not actually sure on the cascade, set null and set default. The way they are written seems to imply to me that it's based on the state of the database before/after the command in question as opposed to the deferred state of the database because of the stuff about updating the state of partially matching rows immediately after the delete/update of the row which wouldn't really make sense when deferred. Does anyone know what other systems do with a case something like this all in a transaction: create table a (a int primary key); create table b (b int references a match full on update cascade on delete cascade deferrable initially deferred); insert into a values (1); insert into a values (2); insert into b values (1); delete from a where a=1; select * from b; commit;
We have to decide how to address this, perhaps with a clearer error message and a TODO item. > Bruce Momjian wrote: > > > Bruce Momjian writes: > > > > > > > ERROR: triggered data change violation on relation "primarytest2" > > > > > > We're getting this report about once every 48 hours, which would make it a > > > FAQ. (hint, hint) > > > > > > > > > First time I heard of it. Does anyone know more details? > > Think I misinterpreted the SQL3 specs WR to this detail. The > checks must be made per statement, not at the transaction > level. I'll try to fix it, but we need to define what will > happen with referential actions in the case of conflicting > actions on the same key - there are some possible conflicts: > > 1. DEFERRED ON DELETE NO ACTION or RESTRICT > > Do the referencing rows reference to the new PK row with > the same key now, or is this still a constraint > violation? I would say it's not, because the constraint > condition is satisfied at the end of the transaction. How > do other databases behave? > > 2. DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT > > Again I'd say that the action should be suppressed > because a matching PK row is present at transaction end - > it's not the same old row, but the constraint itself is > still satisfied. > > Implementing it that way (if it is correct that way) requires > that the RI-triggers check that the key in question really > disappeared from the PK table, at least for the deferred > invocation at transaction end. This lookup is not required in > the immediate case, so it would be possible to retain the > current performance here, but we'd need a mechanism that > tells the trigger if it is actually invoked in immediate or > deferred mode. Don't know how to do that right now. > > To fix it now, I'd tend to remove the triggered data change > check in the trigger queue (where the error is coming from) > and add the extra PK lookup to the triggers for 7.1. Then > think about the suppress of it with an immediate/deferred > flag mechanism for 7.2. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Here is another bug: test=> begin; BEGIN test=> INSERT INTO primarytest2 VALUES (5,5); INSERT 18757 1 test=> UPDATE primarytest2 SET col2=1 WHERE col1 = 5 AND col2 = 5; ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,10) not found > Bruce Momjian wrote: > > > Bruce Momjian writes: > > > > > > > ERROR: triggered data change violation on relation "primarytest2" > > > > > > We're getting this report about once every 48 hours, which would make it a > > > FAQ. (hint, hint) > > > > > > > > > First time I heard of it. Does anyone know more details? > > Think I misinterpreted the SQL3 specs WR to this detail. The > checks must be made per statement, not at the transaction > level. I'll try to fix it, but we need to define what will > happen with referential actions in the case of conflicting > actions on the same key - there are some possible conflicts: > > 1. DEFERRED ON DELETE NO ACTION or RESTRICT > > Do the referencing rows reference to the new PK row with > the same key now, or is this still a constraint > violation? I would say it's not, because the constraint > condition is satisfied at the end of the transaction. How > do other databases behave? > > 2. DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT > > Again I'd say that the action should be suppressed > because a matching PK row is present at transaction end - > it's not the same old row, but the constraint itself is > still satisfied. > > Implementing it that way (if it is correct that way) requires > that the RI-triggers check that the key in question really > disappeared from the PK table, at least for the deferred > invocation at transaction end. This lookup is not required in > the immediate case, so it would be possible to retain the > current performance here, but we'd need a mechanism that > tells the trigger if it is actually invoked in immediate or > deferred mode. Don't know how to do that right now. > > To fix it now, I'd tend to remove the triggered data change > check in the trigger queue (where the error is coming from) > and add the extra PK lookup to the triggers for 7.1. Then > think about the suppress of it with an immediate/deferred > flag mechanism for 7.2. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > Here is another bug: > > test=> begin; > BEGIN > test=> INSERT INTO primarytest2 VALUES (5,5); > INSERT 18757 1 > test=> UPDATE primarytest2 SET col2=1 WHERE col1 = 5 AND col2 = 5; > ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,10) not > found Schema? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
> Bruce Momjian wrote:
> > Here is another bug:
> >
> > test=> begin;
> > BEGIN
> > test=> INSERT INTO primarytest2 VALUES (5,5);
> > INSERT 18757 1
> > test=> UPDATE primarytest2 SET col2=1 WHERE col1 = 5 AND col2 = 5;
> > ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,10) not
> > found
>
> Schema?
>
CREATE TABLE primarytest2 ( col1 INTEGER, col2 INTEGER,
PRIMARY KEY(col1, col2) );
CREATE TABLE foreigntest2 (col3 INTEGER, col4 INTEGER, FOREIGN KEY
(col3,col4) REFERENCES primarytest2 );
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
> -----Original Message-----
> From: Bruce Momjian
>
> > Bruce Momjian wrote:
> > > Here is another bug:
> > >
ISTM commands/trigger.c is broken.
The behabior seems to be changed by recent changes made by Tom.
* Check if we're interested in this row at all * ---------- * ---------- */
*/ ntriggers = rel->trigdesc->n_after_row[event]; if (ntriggers <= 0)
Regards,
Hiroshi Inoue
> > > test=> begin;
> > > BEGIN
> > > test=> INSERT INTO primarytest2 VALUES (5,5);
> > > INSERT 18757 1
> > > test=> UPDATE primarytest2 SET col2=1 WHERE col1 = 5 AND col2 = 5;
> > > ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,10) not
> > > found
> >
> > Schema?
> >
>
> CREATE TABLE primarytest2 (
> col1 INTEGER,
> col2 INTEGER,
> PRIMARY KEY(col1, col2)
> );
>
> CREATE TABLE foreigntest2 (col3 INTEGER,
> col4 INTEGER,
> FOREIGN KEY (col3, col4) REFERENCES
> primarytest2
> );
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> ISTM commands/trigger.c is broken.
> The behabior seems to be changed by recent changes made by Tom.
Hm. I changed the code to not log an AFTER event unless there is
actually a trigger of the relevant type, thus suppressing what I
considered a very serious memory leak in the non-deferred-trigger case.
Are there cases where we must log an event anyway, and if so what are
they? It didn't look to me like the deferred event executor would do
anything with a logged event that has no triggers ...
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > ISTM commands/trigger.c is broken. > > The behabior seems to be changed by recent changes made by Tom. > > Hm. I changed the code to not log an AFTER event unless there is > actually a trigger of the relevant type, thus suppressing what I > considered a very serious memory leak in the non-deferred-trigger case. > Are there cases where we must log an event anyway, and if so what are > they? It didn't look to me like the deferred event executor would do > anything with a logged event that has no triggers ... > Because I don't know details about trigger stuff, I may be misunderstanding. As far as I see, KEY_CHANGED stuff requires to log every event about logged tuples. However I'm suspicious if KEY_CHANGED check is necessary. Removing KEY_CHANGED stuff seems to solve the TODO FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation" though it may introduce other bugs. Regards, Hiroshi Inoue
I wrote:
> Are there cases where we must log an event anyway, and if so what are
> they? It didn't look to me like the deferred event executor would do
> anything with a logged event that has no triggers ...
Oops, I missed the uses of deferredTriggerGetPreviousEvent(). Fixed
now.
regards, tom lane
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Because I don't know details about trigger stuff, I may be
> misunderstanding. As far as I see, KEY_CHANGED stuff
> requires to log every event about logged tuples.
I just realized that myself. The code was still doing it the hard
way (eg, logging *both* before and after events for each tuple),
but it does seem necessary to log all events if there is either an
UPDATE or DELETE deferred trigger.
> However I'm suspicious if KEY_CHANGED check is necessary.
> Removing KEY_CHANGED stuff seems to solve the TODO
> FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
> though it may introduce other bugs.
I suspect it just masks the problem by preventing the trigger code
from executing ...
regards, tom lane
-----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > > However I'm suspicious if KEY_CHANGED check is necessary. > > Removing KEY_CHANGED stuff seems to solve the TODO > > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation" > > though it may introduce other bugs. > > I suspect it just masks the problem by preventing the trigger code > from executing ... > I've examined the new TODO * FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation" a little and am now wondering why it has remained unsolved until now. ISTM there are 2 different RI related issues. 1) "begin; insert; delete(or update pk of) the inserted tuple" causes a "change violation" error. 2) For deferred RI constraints "begin;delete a pk;insert the same pk;commit;" fails(or misbehaves) in case the correspondingfk exist. Shouldn't we distinguish above 2 issues clearly ? And doesn't the new TODO correspond to 1) ? The issue 1) seems to be caused due to the transaction-wide KEY_CHANGED check. Isn't it sufficient to check KEY_CHANGED per query. For example, how about clearing KEY_CHANGED after every DeferredTriggerEndQeury() ? Regards, Hiroshi Inoue