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