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. :)
<bold><color><param>0000,0000,ffff</param>create unique index
usesysid_idx on pg_shadow(usesysid);
CREATE
</color></bold>then i attempt to create a foreign key from pg_class:
<color><param>0000,0000,8080</param>alter table pg_class add constraint
fk_relowner foreign key (relowner)
references pg_shadow(usesysid);
</color>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:
<color><param>ffff,0000,0000</param>testx=# \d pg_shadow
ERROR: Index 'pg_shadow_name_index' does not exist
</color>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
