On Wed, Oct 1, 2014 at 7:57 AM, José Luis Tallón <[email protected]>
wrote:
>
> [snip]
>
> Please excuse my jumping in, but the EXPECTED syntax is:
>
> CREATE INDEX IF NOT EXISTS .....
>
> whereas your current patch implements:
>
> CREATE [IF NOT EXISTS] INDEX ....
>
>
> if I'm reading the grammar correctly.
>
I think it's not wrong. Look at other CINE that already implemented [1] [2].
But CINE for CREATE TABLE is like your proposal [3] :
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF
NOT EXISTS ] table_name ...
So, what's the correct/best grammar?
CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX index_name
or
CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name
> I guess it would be most interesting to implement this minor change for
the next version of the patch. Please do remember to update the
documentation accordingly.
>
I will...
> By the way, you also forgot to remove a previous patch implementing
"namespace_name<DOT>relation_name" for RLS messages. Maybe a rebase is
needed?
>
Sorry... my mistake. Fix attached.
Regards,
[1] http://www.postgresql.org/docs/devel/static/sql-createschema.html
[2] http://www.postgresql.org/docs/devel/static/sql-createsequence.html
[3] http://www.postgresql.org/docs/devel/static/sql-createtable.html
--
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..03a04af 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6427,20 +6427,32 @@ 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;
+
+ /*
+ * Throw an exception when IF NOT EXISTS is used without a named
+ * index
+ */
+ if (n->if_not_exists && n->idxname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("IF NOT EXISTS requires that you name the index."),
+ parser_errposition(@2)));
+
+ 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..076ff8d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2803,7 +2803,7 @@ getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables)
continue;
if (g_verbose)
- write_msg(NULL, "reading row-security enabled for table \"%s\"",
+ write_msg(NULL, "reading row-security enabled for table \"%s\"\n",
tbinfo->dobj.name);
/*
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..0c48d58 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -6,6 +6,12 @@
-- 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 IF NOT EXISTS INDEX ON onek USING btree(unique1 int4_ops);
+ERROR: IF NOT EXISTS requires that you name the index.
+LINE 1: CREATE IF NOT EXISTS INDEX ON onek USING btree(unique1 int4_...
+ ^
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 +2296,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..973a709 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -8,6 +8,10 @@
--
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 IF NOT EXISTS INDEX 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 +715,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
