Thread: Foreign Key bug -- 7.4b4
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
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
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
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 #
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
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