Thread: Problems renaming referencing column

(sorry if it's a dup)

In 7.4.3, if I rename a column which references another table,
constraint trigger fails on update or delete from main table.

There are a couple of similar (and about rename table itself) reports
for 7.0, 7.1 (as Tom Lane said, rename table is fixed in 7.2), but I
see no more reports since 2001.

Here's a simple reproducible example:


fduch@~=# CREATE TABLE master (k integer NOT NULL PRIMARY KEY) WITHOUT OIDS;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master"
CREATE TABLE
fduch@~=# CREATE TABLE slave (ref integer REFERENCES master (k)) WITHOUT OIDS;
CREATE TABLE
fduch@~=# INSERT INTO master VALUES (1);
INSERT 0 1
fduch@~=# INSERT INTO master VALUES (2);
INSERT 0 1
fduch@~=# DELETE FROM master WHERE k = 1;
DELETE 1
fduch@~=# ALTER TABLE slave RENAME ref TO k;
ALTER TABLE
fduch@~=# UPDATE master SET k = 2 where k = 2;
ERROR:  table "slave" does not have column "ref" referenced by constraint "$1"
fduch@~=# DELETE FROM master WHERE k = 2;
ERROR:  table "slave" does not have column "ref" referenced by constraint "$1"


However triggers themselves look good after rename:


fduch@~=# \d slave
     Table "public.slave"
 Column |  Type   | Modifiers
--------+---------+-----------
 k      | integer |
Foreign-key constraints:
    "$1" FOREIGN KEY (k) REFERENCES master(k)

fduch@~=# SELECT * from pg_trigger WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname = 'slave');
 tgrelid |           tgname           | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
tgdeferrable| tginitdeferred | tgnargs | tgattr |                       tgargs 

---------+----------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+----------------------------------------------------
   77304 | RI_ConstraintTrigger_77307 |   1644 |     21 | t         | t              | $1           |         77300 | f
          | f              |       6 |        | $1\000slave\000master\000UNSPECIFIED\000k\000k\000 
(1 row)

fduch@~=# SELECT * from pg_trigger WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname = 'master');
 tgrelid |           tgname           | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
tgdeferrable| tginitdeferred | tgnargs | tgattr |                       tgargs 

---------+----------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+----------------------------------------------------
   77300 | RI_ConstraintTrigger_77309 |   1655 |     17 | t         | t              | $1           |         77304 | f
          | f              |       6 |        | $1\000slave\000master\000UNSPECIFIED\000k\000k\000 
   77300 | RI_ConstraintTrigger_77308 |   1654 |      9 | t         | t              | $1           |         77304 | f
          | f              |       6 |        | $1\000slave\000master\000UNSPECIFIED\000k\000k\000 
(2 rows)


The problem goes away after re-creating the foreign key:

fduch@~=# ALTER TABLE slave DROP CONSTRAINT "$1";
ALTER TABLE
fduch@~=# ALTER TABLE slave ADD CONSTRAINT "$1" FOREIGN KEY (k) REFERENCES master(k);
ALTER TABLE
fduch@~=# DELETE FROM master WHERE k = 2;
DELETE 1


--
Fduch M. Pravking

On Sat, 17 Jul 2004, Alexander M. Pravking wrote:

> (sorry if it's a dup)
>
> In 7.4.3, if I rename a column which references another table,
> constraint trigger fails on update or delete from main table.

I think this probably has to do with the fact that the plan gets cached.

If I close the connection and restart it, the constraint appears to work.

"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> In 7.4.3, if I rename a column which references another table,
> constraint trigger fails on update or delete from main table.

> The problem goes away after re-creating the foreign key:

Actually all you have to do is start a fresh backend.  The problem is
that the "ALTER TABLE slave" fails to force an update of the backend's
relcache entry for "master".  AFAICS this problem has always been there.
Certainly your test case yields a failure of some type in every release
back to 7.0 ...

            regards, tom lane

"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> In 7.4.3, if I rename a column which references another table,
> constraint trigger fails on update or delete from main table.

The following patch (against 7.4.*) appears to fix this problem.

            regards, tom lane

Index: tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.91
diff -c -r1.91 tablecmds.c
*** tablecmds.c    13 Oct 2003 22:47:15 -0000    1.91
--- tablecmds.c    17 Jul 2004 17:16:36 -0000
***************
*** 1534,1539 ****
--- 1534,1553 ----

          CatalogUpdateIndexes(tgrel, tuple);

+         /*
+          * Invalidate trigger's relation's relcache entry so that other
+          * backends (and this one too!) are sent SI message to make them
+          * rebuild relcache entries.  (Ideally this should happen
+          * automatically...)
+          *
+          * We can skip this for triggers on relid itself, since that
+          * relcache flush will happen anyway due to the table or column
+          * rename.  We just need to catch the far ends of RI relationships.
+          */
+         pg_trigger = (Form_pg_trigger) GETSTRUCT(tuple);
+         if (pg_trigger->tgrelid != relid)
+             CacheInvalidateRelcache(pg_trigger->tgrelid);
+
          /* free up our scratch memory */
          pfree(newtgargs);
          heap_freetuple(tuple);