BUG #4428: renaming tables, columns and fk cheks problem

From: "Taras Kopets" <tkopets(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4428: renaming tables, columns and fk cheks problem
Date: 2008-09-20 08:26:24
Message-ID: 200809200826.m8K8QOsS032104@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 4428
Logged by: Taras Kopets
Email address: tkopets(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Guillaume Smet 2008-09-20 10:39:52 Re: BUG #4428: renaming tables, columns and fk cheks problem
Previous Message Magnus Hagander 2008-09-19 14:42:07 Re: [HACKERS] 0x1A in control file on Windows