On Wed, 5 Apr 2006, Pavel Golub wrote:
> 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.
Don't those values reference the attnum(s) of the column(s) which don't
change after a drop column I believe.
----
sszabo=# select * from pg_attribute where attrelid = (select oid from
pg_class where relname='Orders');
attrelid | attname | atttypid | attstattarget | attlen | attnum |
attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign |
attnotnull | atthasdef | attisdropped | attislocal | attinhcount
----------+-----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------
160255 | tableoid | 26 | 0 | 4 | -7 |
0 | -1 | -1 | t | p | i | t
| f | f | t | 0
160255 | cmax | 29 | 0 | 4 | -6 |
0 | -1 | -1 | t | p | i | t
| f | f | t | 0
160255 | xmax | 28 | 0 | 4 | -5 |
0 | -1 | -1 | t | p | i | t
| f | f | t | 0
160255 | cmin | 29 | 0 | 4 | -4 |
0 | -1 | -1 | t | p | i | t
| f | f | t | 0
160255 | xmin | 28 | 0 | 4 | -3 |
0 | -1 | -1 | t | p | i | t
| f | f | t | 0
160255 | ctid | 27 | 0 | 6 | -1 |
0 | -1 | -1 | f | p | s | t
| f | f | t | 0
160255 | OrderID | 23 | -1 | 4 | 1 |
0 | -1 | -1 | t | p | i | t
| t | f | t | 0
160255 | OrderTime | 1114 | -1 | 8 | 2 |
0 | -1 | -1 | f | p | d | f
| f | f | t | 0
160255 | CarID | 23 | -1 | 4 | 3 |
0 | -1 | -1 | t | p | i | f
| t | f | t | 0
(9 rows)
sszabo=#
sszabo=#
sszabo=# ALTER TABLE test."Orders" DROP COLUMN "OrderTime";
ALTER TABLE
sszabo=# select * from pg_attribute where attrelid = (select oid from
pg_class where relname='Orders');
attrelid | attname | atttypid | attstattarget |
attlen | attnum | attndims | attcacheoff | atttypmod | attbyval |
attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal
| attinhcount
----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------
160255 | tableoid | 26 | 0 |
4 | -7 | 0 | -1 | -1 | t | p |
i | t | f | f | t |
0
160255 | cmax | 29 | 0 |
4 | -6 | 0 | -1 | -1 | t | p |
i | t | f | f | t |
0
160255 | xmax | 28 | 0 |
4 | -5 | 0 | -1 | -1 | t | p |
i | t | f | f | t |
0
160255 | cmin | 29 | 0 |
4 | -4 | 0 | -1 | -1 | t | p |
i | t | f | f | t |
0
160255 | xmin | 28 | 0 |
4 | -3 | 0 | -1 | -1 | t | p |
i | t | f | f | t |
0
160255 | ctid | 27 | 0 |
6 | -1 | 0 | -1 | -1 | f | p |
s | t | f | f | t |
0
160255 | OrderID | 23 | -1 |
4 | 1 | 0 | -1 | -1 | t | p |
i | t | t | f | t |
0
160255 | ........pg.dropped.2........ | 0 | 0 |
8 | 2 | 0 | -1 | -1 | f | p |
d | f | f | t | t |
0
160255 | CarID | 23 | -1 |
4 | 3 | 0 | -1 | -1 | t | p |
i | f | t | f | t |
0
(9 rows)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
