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 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, Pennsylvania 19026

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 #

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

-----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

"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 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