May have posted this earlier... 

It would seem that caching the plans for foreign keys has some unwanted
side effects.


test=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.4beta4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
(1 row)
 
test=#
test=# create table a (col integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"
for table "a"
CREATE TABLE
test=#
test=# create table b (col integer primary key references a on update
cascade on delete cascade);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey"
for table "b"
CREATE TABLE
test=#
test=#
test=# insert into a values (1);
INSERT 687978 1
test=# insert into b values (1);
INSERT 687979 1
test=#
test=# insert into a values (2);
INSERT 687980 1
test=# insert into b values (2);
INSERT 687981 1
test=#
test=# delete from a where col = 1;
DELETE 1
test=#
test=# alter table b drop constraint b_pkey;
ALTER TABLE
test=#
test=# delete from a where col = 2;
ERROR:  could not open relation with OID 687972
Fri, 10 Oct 2003 17:34:53 -0400
Rod Taylor <rbt@rbt.ca>
Foreign Key bug -- 7.4b4

I can confirm this bug in CVS.

---------------------------------------------------------------------------

Rod Taylor wrote:
-- Start of PGP signed section.
> May have posted this earlier... 
> 
> It would seem that caching the plans for foreign keys has some unwanted
> side effects.
> 
> 
> test=# select version();
>                                 version
> ------------------------------------------------------------------------
>  PostgreSQL 7.4beta4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
> (1 row)
>  
> test=#
> test=# create table a (col integer primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"
> for table "a"
> CREATE TABLE
> test=#
> test=# create table b (col integer primary key references a on update
> cascade on delete cascade);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey"
> for table "b"
> CREATE TABLE
> test=#
> test=#
> test=# insert into a values (1);
> INSERT 687978 1
> test=# insert into b values (1);
> INSERT 687979 1
> test=#
> test=# insert into a values (2);
> INSERT 687980 1
> test=# insert into b values (2);
> INSERT 687981 1
> test=#
> test=# delete from a where col = 1;
> DELETE 1
> test=#
> test=# alter table b drop constraint b_pkey;
> ALTER TABLE
> test=#
> test=# delete from a where col = 2;
> ERROR:  could not open relation with OID 687972
> 
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 24 Oct 2003 00:26:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Foreign Key bug -- 7.4b4
Bruce Momjian wrote:

> I can confirm this bug in CVS.


Something is cached, if you quit your psql session after
droping the constraint, and you start another psql session
the problem disappear.


Regards
Gaetano Mendola


Mon, 27 Oct 2003 02:07:01 +0100
Gaetano Mendola <mendola@bigfoot.com>
Re: Foreign Key bug -- 7.4b4
Gaetano Mendola wrote:

> Bruce Momjian wrote:
> 
>> I can confirm this bug in CVS.

Dropping the pkey from table b in fact drops the unique index from it. 
The SPI plan cached to check if a row deleted from table a is still 
referenced from table b "can" (and in your case does) use an index scan 
on table b and is thereby corrupted by dropping the pkey.

Switching to a generally non-cached model for all foreign key checks 
would be the only workaround at the moment, and I don't see us doing 
that as it would cause performance to suffer big times for everyone 
who's system doesn't have a permanent "what's the latest schema" contest 
going on.

Since all caching procedural languages and all caching custom C 
functions suffer the same, the correct fix would be to let 
SPI_saveplan() maintain a hash table of all referenced system cache 
objects who's entries point to the referencing saved plans and then mark 
those plans for recompile at system cache invalidation.

I will probably not do it today ... tomorrow doesn't look good either.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Mon, 27 Oct 2003 14:57:31 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Foreign Key bug -- 7.4b4

This this a new TODO?

---------------------------------------------------------------------------

Jan Wieck wrote:
> Gaetano Mendola wrote:
> 
> > Bruce Momjian wrote:
> > 
> >> I can confirm this bug in CVS.
> 
> Dropping the pkey from table b in fact drops the unique index from it. 
> The SPI plan cached to check if a row deleted from table a is still 
> referenced from table b "can" (and in your case does) use an index scan 
> on table b and is thereby corrupted by dropping the pkey.
> 
> Switching to a generally non-cached model for all foreign key checks 
> would be the only workaround at the moment, and I don't see us doing 
> that as it would cause performance to suffer big times for everyone 
> who's system doesn't have a permanent "what's the latest schema" contest 
> going on.
> 
> Since all caching procedural languages and all caching custom C 
> functions suffer the same, the correct fix would be to let 
> SPI_saveplan() maintain a hash table of all referenced system cache 
> objects who's entries point to the referencing saved plans and then mark 
> those plans for recompile at system cache invalidation.
> 
> I will probably not do it today ... tomorrow doesn't look good either.
> 
> 
> Jan
> 
> -- 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Nov 2003 12:18:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Foreign Key bug -- 7.4b4
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> This this a new TODO?

No, it's already there, in multiple guises even.

	o Fix problems with complex temporary table creation/destruction
	  without using PL/PgSQL EXECUTE, needs cache prevention/invalidation

* Flush cached query plans when their underlying catalog data changes

			regards, tom lane

Fri, 07 Nov 2003 12:46:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign Key bug -- 7.4b4 