On Tue, Sep 30, 2014 at 7:01 PM, Josh Berkus <j...@agliodbs.com> wrote: > > On 09/30/2014 02:43 PM, Tom Lane wrote: > > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziome...@gmail.com> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers