The following bug has been logged online:
Bug reference: 2377
Logged by: Pavel Golub
Email address: pavel@microolap.com
PostgreSQL version: 8.1.0
Operating system: Windows XP
Description: pg_constraint didnt't updated when table columns deleted
Details:
To illustrate the bug I'll use such schema:
CREATE TABLE test."Cars"
(
"CarID" SERIAL,
"Model" varchar,
"OrderID" int4 NOT NULL,
PRIMARY KEY ("CarID")
)
WITHOUT OIDS;
CREATE TABLE test."Orders"
(
"OrderID" SERIAL,
"OrderTime" timestamp,
"CarID" int4 DEFAULT 0,
FOREIGN KEY ("CarID")
REFERENCES test."Cars" ("CarID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
Then to fetch information about foreign keys of table test."Orders" I'll use
such query:
SELECT ncon.nspname AS constraint_schema,
c.oid as constraint_table_oid,
c.relname AS constraint_table,
con.conname AS constraint_name,
con.conkey, --this is the column we're watching for
refn.nspname as ref_schema,
refc.oid as ref_table_oid,
refc.relname as ref_table,
con.confkey,
con.confmatchtype AS match_option,
con.confupdtype AS update_rule,
con.confdeltype AS delete_rule,
con.condeferrable,
con.condeferred
FROM pg_namespace ncon
JOIN pg_constraint con ON ncon.oid = con.connamespace
JOIN pg_class c ON con.conrelid = c.oid
JOIN pg_class refc ON con.confrelid = refc.oid
JOIN pg_namespace refn ON refn.oid = refc.relnamespace
WHERE c.relkind = 'r'::"char"
AND con.contype = 'f'::"char"
AND c.oid = 60464 ; --this is test."Orders" OID
This is the returned data:
"test";60464;"Orders";"Orders_CarID_fkey";"{3}";"test";60454;"Cars";"{1}";"u
";"a";"a";f;f
For now all correct. conkey equal 3.
Then we drop "OrderTime" column:
ALTER TABLE test."Orders" DROP COLUMN "OrderTime";
Then execute the same query and get the result:
"test";60464;"Orders";"Orders_CarID_fkey";"{3}";"test";60454;"Cars";"{1}";"u
";"a";"a";f;f
As you can see pg_constraint.conkey column didn't updated.
I didn't check this behavior with other kind of constraints, only on FOREIGN
KEYs.
Regards