comparing output of internal pg tables of referenced tables

Mariel Cherkassky <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com>
Hey,
I tried to get a list of all tables that has a reference to my_table. I
used two different queries :

1)select R.*
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
  AND U.TABLE_NAME = '*my_table*'

2)select conname,  (select r.relname from pg_class r where r.oid =
c.confrelid) as orig_table,    (select array_agg(attname) from pg_attribute
   where attrelid = c.confrelid and ARRAY[attnum] <@ c.conkey) as
orig_cols,  (select r.relname from pg_class r where r.oid = c.conrelid) as
foreign_table,      (select array_agg(attname) from pg_attribute    where
attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as foreign_cols from
pg_constraint c   where c.confrelid = (select oid from pg_class where
relname = '*my_table*') and c.contype='f'

On the second output in the orig_cols I got a few weird outputs like
: {........pg.dropped.5........} or even a columns that doesnt have a
unique index (just a random column from the orig_table).

tried to vacuum the table but still didnt help. The db is at version 9, but
I tried to upgrade it to 10/11/12 and in all versions it stayed the same.

;
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.