Information Schema and constraint names not unique

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Information Schema and constraint names not unique
Date: 2003-11-06 13:00:31
Message-ID: 6.0.0.22.0.20031106235459.065620c8@203.8.195.10
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Just looking at the information schema in 7.4 and noticed something
odd/annoying/problematic:

create table pk(f1 int primary key);
create table fk1(f1 int references pk(f1));
create table fk2(f1 int references pk(f1));

select * from information_schema.referential_constraints;

-[ RECORD 1 ]-------------+----------
constraint_catalog | test
constraint_schema | public
constraint_name | $1
unique_constraint_catalog | test
unique_constraint_schema | public
unique_constraint_name | pk_pkey
match_option | NONE
update_rule | NO ACTION
delete_rule | NO ACTION
-[ RECORD 2 ]-------------+----------
constraint_catalog | test
constraint_schema | public
constraint_name | $1
unique_constraint_catalog | test
unique_constraint_schema | public
unique_constraint_name | pk_pkey
match_option | NONE
update_rule | NO ACTION
delete_rule | NO ACTION

Notice that the two records are identical because the two constraint names
are the same. ISTM that we should have a way of usefully examining specific
constraints without having to name them. Can we add the constraint OID or
some other identifier (table?) or ensure that constraint names are unique?

This problem applies to all the info schema tables that use constraint name.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marcus Meissner 2003-11-06 13:03:34 Re: Erroneous PPC spinlock code
Previous Message Paulo Scardine 2003-11-06 11:00:45 Re: [pgsql-www] Changes to Contributor List