Re: Primary key error in INFORMATION_SCHEMA views

> 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