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 09:44:46 |
Message-ID: | 20040717094446.GA81997@dyatel.antar.bryansk.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander M. Pravking | 2004-07-17 10:08:34 | Problems renaming referencing column |
Previous Message | David Newall | 2004-07-17 07:37:01 | Re: Slow views |