Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id BDFCF632AD6
	for <pgsql-bugs-postgresql.org@mail.postgresql.org>;
	Thu,  2 Sep 2010 15:34:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 52609-07 for
	<pgsql-bugs-postgresql.org@mail.postgresql.org>;
	Thu,  2 Sep 2010 18:34:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from cascavel.ensmp.fr (cascavel.ensmp.fr [194.214.158.227])
	by mail.postgresql.org (Postfix) with ESMTP id 4D59C6329D8
	for <pgsql-bugs@postgresql.org>; Thu,  2 Sep 2010 15:34:50 -0300 (ADT)
Received: from cri.ensmp.fr (85.40.219-88.rev.gaoland.net [88.219.40.85] (may
	be forged)) (authenticated bits=0)
	by cascavel.ensmp.fr (8.14.3/8.14.3/JMMC-11/Mar/2010) with ESMTP id
	o82IYkjp009536
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Thu, 2 Sep 2010 20:34:47 +0200 (MEST)
Date: Thu, 2 Sep 2010 20:34:46 +0200 (CEST)
From: Fabien COELHO <coelho@cri.ensmp.fr>
X-X-Sender: fabien@localhost.localdomain
Reply-To: 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
In-Reply-To: <1283438419.23199.0.camel@vanquo.pezone.net>
Message-ID: <alpine.DEB.2.00.1009021959070.2656@localhost.localdomain>
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>
User-Agent: Alpine 2.00 (DEB 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Miltered: at cascavel with ID 4C7FEE46.000 by Joe's j-chkmail (http : //
	j-chkmail dot ensmp dot fr)!
X-j-chkmail-Enveloppe: 4C7FEE46.000 from
	[88.219.40.85]/85.40.219-88.rev.gaoland.net/88.219.40.85/cri.ensmp.fr/<coelho@cri.ensmp.fr>
X-Scores-Stats: 4C7FEE46.000 B=0.01856 L=0.26403 G=0.00674 Agree    
X-j-chkmail-Score: MSGID : 4C7FEE46.000 on cascavel.ensmp.fr : j-chkmail score
	: . : R=. U=. O=. B=0.007 -> S=0.008
X-j-chkmail-Status: Ham
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201009/17
X-Sequence-Number: 27981


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.