On Tue, Sep 30, 2014 at 7:01 PM, Josh Berkus <[email protected]> wrote:
>
> On 09/30/2014 02:43 PM, Tom Lane wrote:
> > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <[email protected]>
writes:
> >> What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX?
> >
> > It's got the same semantic problems as every other variant of CINE.
> >
> > If there were a huge groundswell of demand for it, maybe we'd hold our
> > noses and do it anyway. But I'm against doing it without that.
>
> This isn't the sort of thing there would ever be a clamor of support
> for, because it's just not that visible of a feature. It's more of a
> regular annoyance for those who encounter it. More importantly, adding
> an IF NOT EXISTS to CREATE INDEX would allow complete idempotent "create
> this bunch of tables" scripts, since now the "create index" statements
> could be included. This would be very nice for schema management tools.
>
> I do think it should be name-based. While an "IF NOT EXISTS" which
> checked for a duplicate column declartion would be nice, there's a raft
> of issues with implementing it that way. Users I know are generally
> just looking to avoid getting a transaction-halting error when they run
> the same create index statement twice.
>
Here is the patch... it's name-based.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..7886729 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
+CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -99,6 +99,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a index with the same name
+ already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>UNIQUE</literal></term>
<listitem>
<para>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, "shared relations must be placed in pg_global tablespace");
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already exists, skipping",
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg("relation \"%s\" already exists",
indexRelationName)));
+ }
/*
* construct tuple descriptor for index tuples
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 160f006..5ef6dcc 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
rel->rd_rel->reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
true, false, false, false,
- true, false, false, true);
+ true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8a1cb4b..a03773b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -610,7 +610,14 @@ DefineIndex(Oid relationId,
stmt->isconstraint, stmt->deferrable, stmt->initdeferred,
allowSystemTableMods,
skip_build || stmt->concurrent,
- stmt->concurrent, !check_rights);
+ stmt->concurrent, !check_rights,
+ stmt->if_not_exists);
+
+ if (!OidIsValid(indexRelationId))
+ {
+ heap_close(rel, NoLock);
+ return indexRelationId;
+ }
/* Add any requested comment */
if (stmt->idxcomment != NULL)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 225756c..39b55db 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2907,6 +2907,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_SCALAR_FIELD(concurrent);
+ COPY_SCALAR_FIELD(if_not_exists);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 905468e..4cf91e0 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1210,6 +1210,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(deferrable);
COMPARE_SCALAR_FIELD(initdeferred);
COMPARE_SCALAR_FIELD(concurrent);
+ COMPARE_SCALAR_FIELD(if_not_exists);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 77d2f29..ff738c3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6427,20 +6427,21 @@ defacl_privilege_target:
* willing to make TABLESPACE a fully reserved word.
*****************************************************************************/
-IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
+IndexStmt: CREATE opt_if_not_exists opt_unique INDEX opt_concurrently opt_index_name
ON qualified_name access_method_clause '(' index_params ')'
opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
- n->unique = $2;
- n->concurrent = $4;
- n->idxname = $5;
- n->relation = $7;
- n->accessMethod = $8;
- n->indexParams = $10;
- n->options = $12;
- n->tableSpace = $13;
- n->whereClause = $14;
+ n->if_not_exists = $2;
+ n->unique = $3;
+ n->concurrent = $5;
+ n->idxname = $6;
+ n->relation = $8;
+ n->accessMethod = $9;
+ n->indexParams = $11;
+ n->options = $13;
+ n->tableSpace = $14;
+ n->whereClause = $15;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 12811a8..ab169c9 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2803,8 +2803,8 @@ getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables)
continue;
if (g_verbose)
- write_msg(NULL, "reading row-security enabled for table \"%s\"",
- tbinfo->dobj.name);
+ write_msg(NULL, "reading row-security enabled for table \"%s\".\"%s\"\n",
+ tbinfo->dobj.namespace->dobj.name, tbinfo->dobj.name);
/*
* Get row-security enabled information for the table.
@@ -2833,8 +2833,8 @@ getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables)
}
if (g_verbose)
- write_msg(NULL, "reading row-security policies for table \"%s\"\n",
- tbinfo->dobj.name);
+ write_msg(NULL, "reading row-security policies for table \"%s\".\"%s\"\n",
+ tbinfo->dobj.namespace->dobj.name, tbinfo->dobj.name);
/*
* select table schema to ensure regproc name is qualified if needed
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 006b180..098ac7d 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -60,7 +60,8 @@ extern Oid index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal);
+ bool is_internal,
+ bool if_not_exists);
extern void index_constraint_create(Relation heapRelation,
Oid indexRelationId,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f3aa69e..a326dc4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2255,6 +2255,7 @@ typedef struct IndexStmt
bool deferrable; /* is the constraint DEFERRABLE? */
bool initdeferred; /* is the constraint INITIALLY DEFERRED? */
bool concurrent; /* should this be a concurrent index build? */
+ bool if_not_exists; /* just do nothing if index already exists */
} IndexStmt;
/* ----------------------
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index a2bef7a..5bd1392 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -6,6 +6,8 @@
-- BTREE
--
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
+CREATE IF NOT EXISTS INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
+NOTICE: relation "onek_unique1" already exists, skipping
CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
@@ -2290,10 +2292,14 @@ create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
CREATE TABLE concur_heap (f1 text, f2 text);
-- empty table
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
+CREATE IF NOT EXISTS INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
+NOTICE: relation "concur_index1" already exists, skipping
INSERT INTO concur_heap VALUES ('a','b');
INSERT INTO concur_heap VALUES ('b','b');
-- unique index
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
+CREATE IF NOT EXISTS UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
+NOTICE: relation "concur_index2" already exists, skipping
-- check if constraint is set up properly to be enforced
INSERT INTO concur_heap VALUES ('b','x');
ERROR: duplicate key value violates unique constraint "concur_index2"
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d4d24ef..2c80605 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -8,6 +8,8 @@
--
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
+CREATE IF NOT EXISTS INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
+
CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
@@ -711,10 +713,12 @@ create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
CREATE TABLE concur_heap (f1 text, f2 text);
-- empty table
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
+CREATE IF NOT EXISTS INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
INSERT INTO concur_heap VALUES ('a','b');
INSERT INTO concur_heap VALUES ('b','b');
-- unique index
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
+CREATE IF NOT EXISTS UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
-- check if constraint is set up properly to be enforced
INSERT INTO concur_heap VALUES ('b','x');
-- check if constraint is enforced properly at build time
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
