From: Fabien COELHO <coelho@cri.ensmp.fr>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
Date: Thu, 2 Sep 2010 20:34:46 +0200 (CEST)
Message-ID: <alpine.DEB.2.00.1009021959070.2656@localhost.localdomain> (raw)
In-Reply-To: <1283438419.23199.0.camel@vanquo.pezone.net>
References: <alpine.DEB.2.00.1008311337530.2449@localhost.localdomain>
<2683.1283266019@sss.pgh.pa.us>
<alpine.DEB.2.00.1009011557480.2570@localhost.localdomain>
<1283369900.15386.0.camel@vanquo.pezone.net>
<alpine.DEB.2.00.1009020741380.2656@localhost.localdomain>
<1283438419.23199.0.camel@vanquo.pezone.net>
Hallo,
>> \d information_schema.referential_constraints
>> View "information_schema.referential_constraints"
>> ...
>> FROM pg_namespace ncon
>> JOIN pg_constraint con ON ncon.oid = con.connamespace
>> JOIN pg_class c ON con.conrelid = c.oid
>
> Well, for the sake of argument, how would you propose to change it?
For the sake of argument:
For the information_schema to be useful, expected keys & foreign keys must
work properly so that the constraints can be joined meaningfully
(otherwise, what is the point?). So any change would have to be be
consistent over all definitions.
The standard expects the triplet <catalog,schema,constraint> to be unique,
but in Pg, <catalog,schema,table,constraint> is unique (I think). Too late
to change that, obviously. So that let building a unique constraint name
just for the information_schema, which is fine with me.
(1) use the OID as the "constraint name" everywhere, it would work, it
would not look so good for display, but it is simple and fast.
(2) otherwise something built on top of <table,constraint>. To be on the
safe side, I would build a string (sql_identifier?) with something like:
quote_ident(<table name>) || '.' || quote_ident(<constraint name>)
And the display would be reasonnable, like : "TableName"."$1" instead of a
big bunch of $1.
The affected information_schema views would be, as far as I can see:
- check_constraint_routine_usage
- check_constraints
- constraint_column_usage
- constraint_table_usage
- domain_constraints
- key_column_usage
- referential_constraints
- table_constraints
With one or two affected columns each.
Note that there may be the same issue with 'triggers' which are also
identified by a <catalog,schema,trigger> triplet. Maybe others.
I can send a patch on the information_schema definition in the coming days
if someone feel that this may deserve a "test". Otherwise, ISTM that it is
a "bug" to be recorded somewhere.
Have a nice day,
--
Fabien.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: coelho@cri.ensmp.fr, peter_e@gmx.net, pgsql-bugs@postgresql.org
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
In-Reply-To: <alpine.DEB.2.00.1009021959070.2656@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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