Re: issue about information_schema REFERENTIAL_CONSTRAINTS

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.