From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: comparing output of internal pg tables of referenced tables
In-reply-to: 
 <CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com>
References: 
 <CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com>
Comments: In-reply-to Mariel Cherkassky <mariel.cherkassky@gmail.com>
	message dated "Thu, 19 Sep 2019 18:50:44 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <24570.1568910502.1@sss.pgh.pa.us>
Date: Thu, 19 Sep 2019 12:28:22 -0400
Message-ID: <24571.1568910502@sss.pgh.pa.us>
Precedence: bulk

Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> 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).

You need to be looking at confkey not conkey for the columns in the
confrelid table.

			regards, tom lane