[BUGS] BUG #2377: pg_constraint didnt't updated when table columns deleted

[Pavel Golub]

The following bug has been logged online:

Bug reference:      2377
Logged by:          Pavel Golub
Email address:      [EMAIL PROTECTED]
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly