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