Skip site navigation (1) Skip section navigation (2)

Re: issue about information_schema REFERENTIAL_CONSTRAINTS

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
Date: 2010-09-02 18:34:46
Message-ID: alpine.DEB.2.00.1009021959070.2656@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-docs
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.

In response to

Responses

pgsql-docs by date

Next:From: Tom LaneDate: 2010-09-02 19:12:10
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
Previous:From: Peter EisentrautDate: 2010-09-02 14:40:19
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS

pgsql-bugs by date

Next:From: Hannu PohjanpaloDate: 2010-09-02 18:36:14
Subject: BUG #5640: ODBC driver installed but not found
Previous:From: Alexsander RosaDate: 2010-09-02 17:54:55
Subject: Re: BUG #5629: ALTER SEQUENCE foo START execute a RESTART

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group