From: | "Sve(at)r" <svear(at)laposte(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Primary key error in INFORMATION_SCHEMA views |
Date: | 2018-05-25 20:51:11 |
Message-ID: | 1527281471072-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
> You haven't actually described the problem you are running into so further
insights that could alleviate your confusion are not possible.
Good evening everyone
I'm sorry because i've got a very poor english (i'm french). I'll try to
explain as soon as possible.
It's my originel fault. I wrote a software to analyze a database and
generate its CDM. This soft is based on the integrity constraints that bind
the tables together. My query that retrieves integrity constraints uses
postgres internal tables "pg_xxx".
For example, here is 4 tables
create table "join_toto"(
"id1_join_toto" integer not null,
"id2_join_toto" integer not null,
primary key("id1_join_toto", "id2_join_toto")
);
create table "base_toto"(
"id_base_toto" integer not null,
"ref1_base_toto" integer not null,
"ref2_base_toto" integer not null,
constraint "fk_xxx" foreign key ("ref1_base_toto", "ref2_base_toto")
references "join_toto"("id2_join_toto", "id1_join_toto") match full
on update cascade on delete cascade,
primary key("id_base_toto")
);
create table "join_titi"(
"id1_join_titi" integer not null,
"id2_join_titi" integer not null,
primary key("id1_join_titi", "id2_join_titi")
);
create table "base_titi"(
"id_base_titi" integer not null,
"ref1_base_titi" integer not null,
"ref2_base_titi" integer not null,
constraint "fk_xxx" foreign key ("ref1_base_titi", "ref2_base_titi")
references "join_titi"("id2_join_titi", "id1_join_titi") match full
on update cascade on delete cascade,
primary key("id_base_titi")
);
So, as you can see, "base_toto" is linked to only "join_toto" and
"base_titi" is linked to only "join_titi".
I want to know the link of "base_toto"
select
pg_constraint.conrelid,
pg_constraint.confrelid,
pg_constraint.conkey,
pg_constraint.confkey,
t2.schemaname,
t2.relname
from pg_constraint
inner join pg_stat_user_tables as t1 on (t1.relid=pg_constraint.conrelid)
inner join pg_stat_user_tables as t2 on (t2.relid=pg_constraint.confrelid)
where (pg_constraint.contype, t1.schemaname, t1.relname)=('f', 'public',
'base_toto')
It's ok. I can see "base_toto" linked to "join_toto". But this sql using
"pg" internal's tables. There tables can change in the future with the
Postgres's version.
SQLPro, in forum, says "you need use normalized views in information_schema
because they never change".
So i've tried long long time to find any request with information_schema's
views. Like that
select distinct
k.table_schema,
k.table_name,
t.table_schema,
t.table_name
from information_schema.table_constraints as t
inner join (information_schema.referential_constraints as r
inner join information_schema.key_column_usage as k
on (
(k.constraint_schema, k.constraint_name)=(r.unique_constraint_schema,
r.unique_constraint_name)
)
) on (
(r.constraint_schema, r.constraint_name)=(t.constraint_schema,
t.constraint_name)
)
where (t.table_schema, t.table_name)=('public', 'base_toto');
But this request can't returns true's result because the constraint's name
"fk_xxx" is not unique in schema. So this request returns "base_toto" is
linked to "join_toto" and "join_titi" and that's false.
It is therefore unfortunate that Postgres' normalized views can not return a
result that allows me to know exactly which table is linked to "base_toto".
Best regards
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2018-05-25 21:45:58 | Re: Primary key error in INFORMATION_SCHEMA views |
Previous Message | David G. Johnston | 2018-05-25 19:42:22 | Re: BUG #15211: Urjent |