pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcomparing output of internal pg tables of referenced tables
2+ messages / 2 participants
[nested] [flat]
* comparing output of internal pg tables of referenced tables
@ 2019-09-19 15:50 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mariel Cherkassky @ 2019-09-19 15:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: comparing output of internal pg tables of referenced tables
@ 2019-09-19 16:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2019-09-19 16:28 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-09-19 16:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-19 15:50 comparing output of internal pg tables of referenced tables Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-09-19 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox