To: Susanne Ebrecht <susanne@2ndQuadrant.com>
cc: Ingmar Brouns <swingi@gmail.com>, pgsql-bugs@postgresql.org,
	Peter Eisentraut <peter_e@gmx.net>
Subject: Re: BUG #5926: information schema dtd_identifier for element_types,
	columns, parameters views inconsistent
In-reply-to: <4DDCE812.10401@2ndQuadrant.com> 
References: <201103111318.p2BDITcL034900@wwwmaster.postgresql.org>
	<4DDCE812.10401@2ndQuadrant.com>
Comments: In-reply-to Susanne Ebrecht <susanne@2ndQuadrant.com>
	message dated "Wed, 25 May 2011 13:29:22 +0200"
Date: Wed, 08 Jun 2011 11:09:27 -0400
Message-ID: <9773.1307545767@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

> On 11.03.2011 14:18, Ingmar Brouns wrote:
>> ... The reason seems to be
>> that the dtd_identifier of the element_types view has prepended 'a's whereas
>> the dtd_identifiers of the columns and parameter views do not:
>> 
>> select column_name,dtd_identifier
>> from information_schema.columns c
>> where c.table_schema = 'public'
>> and   c.table_name   = 'test_table';
>> 
>> column_name | dtd_identifier
>> -------------+----------------
>> a           | 1
>> b           | 2
>> c           | 3
>> (3 rows)
>> 
>> select dtd_identifier
>> from information_schema.element_types e
>> where e.object_schema = 'public'
>> and e.object_name = 'test_table';
>> 
>> dtd_identifier
>> ----------------
>> a1
>> a3
>> (2 rows)
>> 
>> The element_types view has a column 'collection_type_identifier', this
>> column is not present in the documentation. It is defined exactly as the
>> dtd_identifier, only then without the prepended 'a':

The omission of collection_type_identifier from the docs is clearly a
doc bug.  However, it looks to me like you've identified an error in the
view definition, not only a doc bug.  I think the values of the
dtd_identifier and collection_type_identifier columns are swapped, ie,
we ought to be prepending 'a' to the collection_type_identifier not the
dtd_identifier.  As far as I can tell from the spec, dtd_identifier
ought to be the identifier of the element type, while
collection_type_identifier should be a made-up identifier for the array
type.  That would make the sample query given in the docs correct.

If my analysis is correct, we really ought to try to fix this in time
for beta2, since there's no way to fix it without a forced initdb.

Comments?

			regards, tom lane