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

Tue, 12 Dec 2000 12:13:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
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/


Wed, 13 Dec 2000 21:09:17 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: 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
  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

Wed, 13 Dec 2000 23:17:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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.                                  #
#================================================== JanWieck@Yahoo.com #



Thu, 14 Dec 2000 07:02:24 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
Re: 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
>   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

Mon, 22 Jan 2001 23:16:48 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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.                                  #
> #================================================== 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

Mon, 22 Jan 2001 23:17:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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


Tue, 23 Jan 2001 14:31:24 +0600 (NS)
Max Khon <fjoe@iclub.nsu.ru>
Re: 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;


Tue, 23 Jan 2001 10:41:21 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug in FOREIGN KEY

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

Wed, 24 Jan 2001 09:44:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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.                                  #
> #================================================== 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

Fri, 26 Jan 2001 16:10:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Fri, 26 Jan 2001 17:02:32 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: 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
  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

Fri, 26 Jan 2001 17:03:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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
>   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
> 

Sat, 27 Jan 2001 07:43:59 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Bug in FOREIGN KEY
"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

Fri, 26 Jan 2001 18:13:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in FOREIGN KEY 
> -----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

Sat, 27 Jan 2001 13:29:30 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: 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

Sat, 27 Jan 2001 00:20:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in FOREIGN KEY 
"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

Sat, 27 Jan 2001 00:25:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in FOREIGN KEY 
-----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

Sun, 28 Jan 2001 07:21:26 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Bug in FOREIGN KEY 