hubert depesz lubaczewski <depesz@depesz.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
