Re: [HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> 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).

> Weren't you proposing elsewhere to split pg_constraint in two catalogs,
> one for table constraint and another for domain constraints?  That seems
> a cleaner solution to me.

Yeah, and you'll notice how much progress we've made towards that.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Alvaro Herrera]

Tom Lane wrote:

> 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).

Weren't you proposing elsewhere to split pg_constraint in two catalogs,
one for table constraint and another for domain constraints?  That seems
a cleaner solution to me.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Tom Lane]

Andrew Dunstan  writes:
> On 03/30/2016 10:21 AM, Tom Lane wrote:
>> 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.

> +1, but does that mean people will have to change constraint names to be 
> compliant before running pg_upgrade?

Yeah, but I think the situation is pretty uncommon, because we already
reject duplicate constraint names in most cases.  As far as I could see
in testing it earlier, these cases all fail already:

* create index constraint when same-named index constraint exists already
* create FK constraint when same-named index constraint exists already
* create FK constraint when same-named FK constraint exists already
* create check constraint when same-named check constraint exists already
* create FK constraint when same-named check constraint exists already

I think that the case Amit's patch plugged, namely create index constraint
when same-named FK or check constraint exists already, may be about the
only missing check.  I just want a unique index to be sure we are covering
all cases.

Note also that because pg_dump prefers to create indexes before FK
constraints (for obvious reasons), I believe that such a case would
fail to dump/restore or pg_upgrade already.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Andrew Dunstan]

On 03/30/2016 10:21 AM, Tom Lane wrote:

Amit Langote  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

Re: [HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Tom Lane]

Amit Langote  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.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Amit Langote]

On 2016/03/30 15:16, Harshal Dhumal wrote:
> Hi Team,
> 
> While I was  working on constraints node in pgadmin4 I came across this
> scenario. Please let me know if it's correct behaviour or a bug.
> 
> *Scenario:*
> 
> 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.
> e.g.
> 
> ALTER TABLE public."Test_tbl" DROP CONSTRAINT key;
> 
> 
> If we execute above query for first time then it drops primary key first
> and if execute same query for second time then it drops foreign key.
> 
> Also in pgadmin3 if we right click on foreign key and try to drop it, it
> drops primary key and not the foreign key. We have to drop foreign key
> twice to actually drop the foreign key if primary key with same was there.

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)

Maybe, we should perform the constraint name check in code paths handling
the following two cases:

ALTER TABLE foo ADD CONSTRAINT name PK/UNIQUE/EXCLUSION ...

ALTER TABLE foo ADD CONSTRAINT name PK/UNIQUE/EXCLUSION USING INDEX ...

If so, attached a patch for the same (regression tests pass).

Thanks,
Amit
>From 8cb464110747b43a323ce8b819882ed0265233de Mon Sep 17 00:00:00 2001
From: amit 
Date: Wed, 30 Mar 2016 16:59:35 +0900
Subject: [PATCH] Perform constraint name uniqueness check for index constraints.

---
 src/backend/commands/indexcmds.c |   14 ++
 src/backend/commands/tablecmds.c |   13 +
 2 files changed, 27 insertions(+), 0 deletions(-)

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 13b04e6..4528378 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -23,6 +23,7 @@
 #include "catalog/index.h"
 #include "catalog/indexing.h"
 #include "catalog/pg_am.h"
+#include "catalog/pg_constraint_fn.h"
 #include "catalog/pg_opclass.h"
 #include "catalog/pg_opfamily.h"
 #include "catalog/pg_tablespace.h"
@@ -467,6 +468,19 @@ DefineIndex(Oid relationId,
 			stmt->excludeOpNames,
 			stmt->primary,
 			stmt->isconstraint);
+	/*
+	 * Prevent creation of the constraint entry with duplicate name further
+	 * down by index_create().
+	 */
+	else if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
+			 RelationGetRelid(rel),
+			 RelationGetNamespace(rel),
+			 indexRelationName))
+		ereport(ERROR,
+(errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for relation \"%s\" already exists",
+		indexRelationName,
+		RelationGetRelationName(rel;
 
 	/*
 	 * look up the access method, verify it can handle the requested features
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 96dc923..b519349 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5983,6 +5983,19 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel,
 	constraintName = stmt->idxname;
 	if (constraintName == NULL)
 		constraintName = indexName;
+	/*
+	 * Prevent creation of the constraint entry with duplicate name below
+	 * by index_constraint_create().
+	 */
+	else if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
+			 RelationGetRelid(rel),
+			 RelationGetNamespace(rel),
+			 constraintName))
+		ereport(ERROR,
+(errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for relation \"%s\" already exists",
+		constraintName,
+		RelationGetRelationName(rel;
 	else if (strcmp(constraintName, indexName) != 0)
 	{
 		ereport(NOTICE,
-- 
1.7.1


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [postgresSQL] [bug] Two or more different types of constraints with same name creates ambiguity while drooping.

[Harshal Dhumal]

Hi Team,

While I was  working on constraints node in pgadmin4 I came across this
scenario. Please let me know if it's correct behaviour or a bug.

*Scenario:*

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.
e.g.

ALTER TABLE public."Test_tbl" DROP CONSTRAINT key;


If we execute above query for first time then it drops primary key first
and if execute same query for second time then it drops foreign key.

Also in pgadmin3 if we right click on foreign key and try to drop it, it
drops primary key and not the foreign key. We have to drop foreign key
twice to actually drop the foreign key if primary key with same was there.


*Note: Create foreign key first and then primary key with same name.*


Regards

-- 
*Harshal Dhumal*
*Software Engineer *



EenterpriseDB