The following bug has been logged online:
Bug reference: 4428
Logged by: Taras Kopets
Email address: tkopets@gmail.com
PostgreSQL version: 8.3.1
Operating system: Win XP SP2
Description: renaming tables, columns and fk cheks problem
Details:
-- a simple test case to reproduce the problem
CREATE TABLE fktest_a(a_id integer NOT NULL PRIMARY KEY);
CREATE TABLE fktest_b
(
b_id integer NOT NULL,
a_id integer NOT NULL,
PRIMARY KEY (b_id),
FOREIGN KEY (a_id) REFERENCES fktest_a (a_id)
);
INSERT INTO fktest_a VALUES(1);
INSERT INTO fktest_a VALUES(2);
INSERT INTO fktest_a VALUES(3);
INSERT INTO fktest_b VALUES(1,3);
INSERT INTO fktest_b VALUES(2,1);
INSERT INTO fktest_b VALUES(3,2);
INSERT INTO fktest_b VALUES(4,2);
INSERT INTO fktest_b VALUES(5,3);
-- INSERT INTO fktest_b VALUES(6,4); -- error (fk violation), that's
good!
ALTER TABLE fktest_a RENAME TO fka;
ALTER TABLE fka RENAME a_id TO aid;
ALTER TABLE fktest_b RENAME TO fkb;
ALTER TABLE fkb RENAME a_id TO aid;
ALTER TABLE fkb RENAME b_id TO bid;
INSERT INTO fkb VALUES(6,4); -- stange error (see below)
-- ERROR: relation "public.fktest_a" does not exist
-- SQL state: 42P01
-- Context: SQL statement "SELECT 1 FROM ONLY "public"."fktest_a" x WHERE
"a_id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
-- but if we start new session and put the same insert statement we will get
our expected fk violation error
-- clean-up
-- DROP TABLE fkb;
-- DROP TABLE fka;
sorry to bother you if I'm wrong