Date: Fri, 3 Sep 2010 14:08:36 +0200 (CEST)
From: Fabien COELHO <coelho@cri.ensmp.fr>
Reply-To: Fabien COELHO <coelho@cri.ensmp.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Peter Eisentraut <peter_e@gmx.net>,
	PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS 
In-Reply-To: <6462.1283454730@sss.pgh.pa.us>
Message-ID: <alpine.DEB.2.00.1009031348321.2448@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>
	<alpine.DEB.2.00.1009021959070.2656@localhost.localdomain>
	<6462.1283454730@sss.pgh.pa.us>
User-Agent: Alpine 2.00 (DEB 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed


Dear Tom,

Still for the sake of argument:

> Note that (2) fails for long names; you have to do something to
> compress to NAMEDATALEN.

Indeed.

What if the type is changed to TEXT? It is just a view after all.
How important is it to stick to "sql_identifier"?


> The big problem with either of these is that it's no longer easily
> possible to extract the actual constraint name from the view.

Sure. A function is provided to do so, say
information_schema_constraint_name_to_pg_constraint_name.

> In any case, I am fairly sure that not having the constraint_name column 
> show the actual constraint name is a violation of the spirit of the SQL 
> spec, whether or not you can claim that it meets the letter.

Well, one must choose between to evil:

  (1) the constraint_name is changed in the view to be unique as expected
      by the spec, and the data can be joined meaningfully, and some reliable
      information can be derived.

  (2) the constraint_name looks nice but is not unique, and
      the information in the view is ambiguous and cannot be relied upon,
      so one is back to square "postgresql supports the information_schema,
      but there is no point to query it and expecting the results to
      reflect the contents of the catalogs".

If you want to stick to both the letter and the spirit of the spec, that 
would mean enforcing unique constraint names in pg and break every 
applications. Not good.

ISTM that the "spirit" of the information schema is more to be useful (1) 
than to look beautiful (2).

Another technical proposal, a little more subtle and with possible 
underlying issues I cannot foresee: have the constraint_name be a "pair of 
sql_identifiers".

-- 
Fabien.