RE: [HACKERS] Bug in FOREIGN KEY
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] 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 corresponding fk 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
Re: [HACKERS] Bug in FOREIGN KEY
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. # #== [EMAIL PROTECTED] # -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Bug in FOREIGN KEY
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. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [HACKERS] Bug in FOREIGN KEY
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [HACKERS] Bug in FOREIGN KEY
-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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Bug in FOREIGN KEY
"Hiroshi Inoue" [EMAIL PROTECTED] 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
RE: [HACKERS] Bug in FOREIGN KEY
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] "Hiroshi Inoue" [EMAIL PROTECTED] 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
Re: [HACKERS] Bug in FOREIGN KEY
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
Re: [HACKERS] Bug in FOREIGN KEY
"Hiroshi Inoue" [EMAIL PROTECTED] 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
Re: [HACKERS] Bug in FOREIGN KEY
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
Re: [HACKERS] Bug in FOREIGN KEY
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;
Re: [HACKERS] Bug in FOREIGN KEY
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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Bug in FOREIGN KEY
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. # #== [EMAIL PROTECTED] # -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Bug in FOREIGN KEY
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. # #== [EMAIL PROTECTED] #
Re: [HACKERS] Bug in FOREIGN KEY
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 [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Bug in FOREIGN KEY
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
