Thread: Foreign Key bug -- 7.4b4

Foreign Key bug -- 7.4b4

From
Rod Taylor
Date:
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,
compiledby 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


Re: Foreign Key bug -- 7.4b4

From
Bruce Momjian
Date:
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,
Pennsylvania19073
 


Re: Foreign Key bug -- 7.4b4

From
Gaetano Mendola
Date:
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



Re: Foreign Key bug -- 7.4b4

From
Jan Wieck
Date:
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 #



Re: Foreign Key bug -- 7.4b4

From
Bruce Momjian
Date:
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,
Pennsylvania19073
 


Re: Foreign Key bug -- 7.4b4

From
Tom Lane
Date:
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