comparing output of internal pg tables of referenced tables

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: comparing output of internal pg tables of referenced tables
Date: 2019-09-19 15:50:44
Message-ID: CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

;

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joao Junior 2019-09-19 15:59:55 Delete huge Table under XFS
Previous Message Tom Lane 2019-09-19 14:11:52 Re: Surprising benchmark count(1) vs. count(*)