Re: Bug when changing datatype of primary key column

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> $ create table z (i int4);
> CREATE TABLE
> $ create unique index q on z (i);
> CREATE INDEX
> $ alter table z add primary key using index q;
> ALTER TABLE
> $ alter table z alter column i type int8;
> ERROR: could not open relation with OID 16503

> looks like some missing dependancy.

Mph. Looks more like too many dependencies :-(

If you just create a pkey straight off, the dependencies look like this:

regression=# create domain ref as int;
CREATE DOMAIN
regression=# create table z (i int primary key);
CREATE TABLE
regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg_depend where objid > 'ref'::regtype or refobjid > 'ref'::regtype;
obj | ref | deptype
------------------------------+------------------------------+---------
type z | table z | i
type z[] | type z | i
table z | schema public | n
constraint z_pkey on table z | table z column i | a
index z_pkey | constraint z_pkey on table z | i
(5 rows)

But if you make the index separately and then use "add primary key using
index":

regression=# drop table z;
DROP TABLE
regression=# create table z (i int4);
CREATE TABLE
regression=# create unique index q on z (i);
CREATE INDEX
regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg_depend where objid > 'ref'::regtype or refobjid > 'ref'::regtype;
obj | ref | deptype
----------+------------------+---------
type z | table z | i
type z[] | type z | i
table z | schema public | n
index q | table z column i | a
(4 rows)

regression=# alter table z add primary key using index q;
ALTER TABLE
regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg_depend where objid > 'ref'::regtype or refobjid > 'ref'::regtype;
obj | ref | deptype
-------------------------+-------------------------+---------
type z | table z | i
type z[] | type z | i
table z | schema public | n
index q | table z column i | a
constraint q on table z | table z column i | a
index q | constraint q on table z | i
(6 rows)

So that ALTER is forgetting to remove the index's original direct
dependency on the table column(s). I suppose ideally that wouldn't
matter, but in the real world it will likely confuse many things, not
just ALTER COLUMN TYPE.

regards, tom lane