hi, i have a question about pg_shadow and constraints on it.
someone left our employ and i did a dropuser on his id. later,
when i did a pg_dump none of the tables created by him were
dumped. i added him back thru createuser so that i could do a
valid pg_dump.
i tried to create a unique index on pg_shadow on usesysid with
the intention of creating a foreign key from pg_class to the
shadow table to save me from myself. :)
create unique index usesysid_idx on pg_shadow(usesysid);
CREATE
then i attempt to create a foreign key from pg_class:
alter table pg_class add constraint fk_relowner foreign key (relowner)
references pg_shadow(usesysid);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create
implicit trigger(s) for FOREIGN KEY check(s)
ERROR: Index 'pg_shadow_name_index' does not exist
so i do a \d on pg_shadow:
testx=# \d pg_shadow
ERROR: Index 'pg_shadow_name_index' does not exist
there were no indexes on pg_shadow when i started and i don't know why i
would be getting this index error message after creating a unique index.
i'm guessing that i cannot create such an index on pg_shadow because it causes
some sort of internal problems. what i wanted to do was create a "STOP" for
attempting to drop a user if that user still owns objects? with a lot of
databases i thought it'd be easier for the system to tell me if such a
situation existed than my searching through for that info.
any suggestions would be welcome!
mikeo
