Skip site navigation (1) Skip section navigation (2)

Problems renaming referencing column

From: "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problems renaming referencing column
Date: 2004-07-17 10:08:34
Message-ID: 20040717100834.GB81997@dyatel.antar.bryansk.ru (view raw or flat)
Thread:
Lists: pgsql-bugs
(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(at)~=# 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(at)~=# CREATE TABLE slave (ref integer REFERENCES master (k)) WITHOUT OIDS;
CREATE TABLE
fduch(at)~=# INSERT INTO master VALUES (1);
INSERT 0 1
fduch(at)~=# INSERT INTO master VALUES (2);
INSERT 0 1
fduch(at)~=# DELETE FROM master WHERE k = 1;
DELETE 1
fduch(at)~=# ALTER TABLE slave RENAME ref TO k;
ALTER TABLE
fduch(at)~=# UPDATE master SET k = 2 where k = 2;
ERROR:  table "slave" does not have column "ref" referenced by constraint "$1"
fduch(at)~=# 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(at)~=# \d slave
     Table "public.slave"
 Column |  Type   | Modifiers
--------+---------+-----------
 k      | integer |
Foreign-key constraints:
    "$1" FOREIGN KEY (k) REFERENCES master(k)

fduch(at)~=# 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(at)~=# 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(at)~=# ALTER TABLE slave DROP CONSTRAINT "$1";
ALTER TABLE
fduch(at)~=# ALTER TABLE slave ADD CONSTRAINT "$1" FOREIGN KEY (k) REFERENCES master(k);
ALTER TABLE
fduch(at)~=# DELETE FROM master WHERE k = 2;
DELETE 1


-- 
Fduch M. Pravking

Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2004-07-17 15:08:52
Subject: Re: Problems renaming referencing column
Previous:From: Alexander M. PravkingDate: 2004-07-17 09:44:46
Subject: Problems renaming referencing column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group