On 03/30/2016 10:21 AM, Tom Lane wrote:
Amit Langote <langote_amit...@lab.ntt.co.jp> writes:
On 2016/03/30 15:16, Harshal Dhumal wrote:
If we create two different type of constrains (lets say primary key and
foreign key) on same table with same name (lets say 'key' ) then its shows
same drop query for both constrains.
I have a vague recollection that non-uniqueness of constraint names may
have been intentional at some point. But yeah, the existence of the
ALTER TABLE DROP CONSTRAINT syntax seems to make that a pretty bad idea.
It seems that, whereas name uniqueness check occurs when creating a named
FK constraint, the same does not occur when creating a named PK constraint
or any index-based constraint for that matter (they are handled by
different code paths - in the latter's case, name conflicts with existing
relations is checked for when creating the constraint index)
I think that if we want to ensure uniqueness of constraint names, this
is really approaching it the wrong way, as it still fails to provide
any guarantees (consider concurrent index creation, for example).
What we need is a unique index on pg_constraint.
The problem with that is that pg_constraint contains both table-related
and type (domain) related constraints; but it strikes me that we could
probably create a unique index on (conrelid, contypid, conname). Given
the convention that conrelid is zero in a type constraint and contypid
is zero in a table constraint, this should work to enforce per-table
or per-type constraint name uniqueness. The cost of an extra index
is a bit annoying, but we could probably make it help pay for itself
by speeding up assorted searches.
+1, but does that mean people will have to change constraint names to be
compliant before running pg_upgrade?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
