Thread

pg_shadow constraint ?

mikeo <mikeo@spectrumtelecorp.com> — 2000-07-05T19:55:05Z

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