Re: BUG #4417: Foreign keys do not work after altering table/column names

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Benjamin Bihler wrote:
>> Unfortunately it is not possible to give more details, since these scripts
>> are part of a commercial product.

> It's unlikely that anyone can help you without more details.

Although the OP could certainly have worked a bit harder at providing a
self-contained example, it's not hard to reproduce a problem:

regression=# create table atab(col int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "atab_pkey" for table "atab"
CREATE TABLE
regression=# create table btab(col int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "btab_pkey" for table "btab"
CREATE TABLE
regression=# alter table atab add column bref int references btab;
ALTER TABLE
regression=# insert into atab values(1,2);
ERROR:  insert or update on table "atab" violates foreign key constraint "atab_bref_fkey"
DETAIL:  Key (bref)=(2) is not present in table "btab".
regression=# insert into btab values(2);
INSERT 0 1
regression=# insert into atab values(1,2);
INSERT 0 1
regression=# alter table btab rename to b_new;
ALTER TABLE
regression=# alter table b_new rename col to col_new;
ALTER TABLE
regression=# update atab set bref = 3;

At this point 8.2 gives the expected error:

ERROR:  insert or update on table "atab" violates foreign key constraint "atab_bref_fkey"
DETAIL:  Key (bref)=(3) is not present in table "b_new".

but in CVS HEAD I get:

ERROR:  relation "public.btab" does not exist
LINE 1: SELECT 1 FROM ONLY "public"."btab" x WHERE "col" OPERATOR(pg...
                           ^
QUERY:  SELECT 1 FROM ONLY "public"."btab" x WHERE "col" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x

What is evidently happening is that the plancache decides it needs to
redo the cached plan for this query, but it's regenerating it from
a static version of the query text.

8.2 is more or less accidentally failing to fail: it's got a cached
plan, which it is too stupid to try to replan, but that plan still
works because it's referring to B by OID and attnum.  There are huge
numbers of cases where 8.2 will fail miserably on alterations of the
tables involved in the FK, but simple renaming isn't one of them.
Whereas 8.3 handles all the other cases and falls down on renaming :-(

So I guess what we'd need to fix this is some sort of wart added to the
plancache mechanism that would allow the RI triggers to regenerate their
query text, not only update the derived plan, when a plan invalidation
event occurs.

My first thought was some kind of callback function to construct the
query text afresh, but that seems pretty baroque.  It'd be simpler
to just have an option to let plancache.c return a failure indication
when its plan is obsolete, whereupon ri_triggers.c discards that plan
and builds a fresh one.  [ pokes around... ]  Hm, the fly in that
ointment is that ri_triggers and plancache aren't communicating directly
but through SPI.  I'm really loath to change the SPI API for this;
is there another way?

            regards, tom lane