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 <amitlangot...@gmail.com>
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
