Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit08309aa

Browse files
committed
Implement IF NOT EXIST for CREATE INDEX.
Fabrízio de Royes Mello, reviewed by Marti Raudsepp, Adam Brightwell and me.
1 parent171c377 commit08309aa

File tree

11 files changed

+85
-5
lines changed

11 files changed

+85
-5
lines changed

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
24+
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [[ IF NOT EXISTS ]<replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
2525
( { <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 } ] [, ...] )
2626
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
2727
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -126,6 +126,18 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</
126126
</listitem>
127127
</varlistentry>
128128

129+
<varlistentry>
130+
<term><literal>IF NOT EXISTS</literal></term>
131+
<listitem>
132+
<para>
133+
Do not throw an error if a relation with the same name already exists.
134+
A notice is issued in this case. Note that there is no guarantee that
135+
the existing index is anything like the one that would have been created.
136+
Index name is required when <literal>IF NOT EXISTS</literal> is specified.
137+
</para>
138+
</listitem>
139+
</varlistentry>
140+
129141
<varlistentry>
130142
<term><replaceable class="parameter">name</replaceable></term>
131143
<listitem>

‎src/backend/catalog/index.c

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -674,6 +674,8 @@ UpdateIndexRelation(Oid indexoid,
674674
*will be marked "invalid" and the caller must take additional steps
675675
*to fix it up.
676676
* is_internal: if true, post creation hook for new index
677+
* if_not_exists: if true, do not throw an error if a relation with
678+
* the same name already exists.
677679
*
678680
* Returns the OID of the created index.
679681
*/
@@ -697,7 +699,8 @@ index_create(Relation heapRelation,
697699
boolallow_system_table_mods,
698700
boolskip_build,
699701
boolconcurrent,
700-
boolis_internal)
702+
boolis_internal,
703+
boolif_not_exists)
701704
{
702705
OidheapRelationId=RelationGetRelid(heapRelation);
703706
Relationpg_class;
@@ -773,10 +776,22 @@ index_create(Relation heapRelation,
773776
elog(ERROR,"shared relations must be placed in pg_global tablespace");
774777

775778
if (get_relname_relid(indexRelationName,namespaceId))
779+
{
780+
if (if_not_exists)
781+
{
782+
ereport(NOTICE,
783+
(errcode(ERRCODE_DUPLICATE_TABLE),
784+
errmsg("relation \"%s\" already exists, skipping",
785+
indexRelationName)));
786+
heap_close(pg_class,RowExclusiveLock);
787+
returnInvalidOid;
788+
}
789+
776790
ereport(ERROR,
777791
(errcode(ERRCODE_DUPLICATE_TABLE),
778792
errmsg("relation \"%s\" already exists",
779793
indexRelationName)));
794+
}
780795

781796
/*
782797
* construct tuple descriptor for index tuples

‎src/backend/catalog/toasting.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
342342
rel->rd_rel->reltablespace,
343343
collationObjectId,classObjectId,coloptions, (Datum)0,
344344
true, false, false, false,
345-
true, false, false, true);
345+
true, false, false, true, false);
346346

347347
heap_close(toast_rel,NoLock);
348348

‎src/backend/commands/indexcmds.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -610,7 +610,14 @@ DefineIndex(Oid relationId,
610610
stmt->isconstraint,stmt->deferrable,stmt->initdeferred,
611611
allowSystemTableMods,
612612
skip_build||stmt->concurrent,
613-
stmt->concurrent, !check_rights);
613+
stmt->concurrent, !check_rights,
614+
stmt->if_not_exists);
615+
616+
if (!OidIsValid(indexRelationId))
617+
{
618+
heap_close(rel,NoLock);
619+
returnindexRelationId;
620+
}
614621

615622
/* Add any requested comment */
616623
if (stmt->idxcomment!=NULL)

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2907,6 +2907,7 @@ _copyIndexStmt(const IndexStmt *from)
29072907
COPY_SCALAR_FIELD(deferrable);
29082908
COPY_SCALAR_FIELD(initdeferred);
29092909
COPY_SCALAR_FIELD(concurrent);
2910+
COPY_SCALAR_FIELD(if_not_exists);
29102911

29112912
returnnewnode;
29122913
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1210,6 +1210,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
12101210
COMPARE_SCALAR_FIELD(deferrable);
12111211
COMPARE_SCALAR_FIELD(initdeferred);
12121212
COMPARE_SCALAR_FIELD(concurrent);
1213+
COMPARE_SCALAR_FIELD(if_not_exists);
12131214

12141215
return true;
12151216
}

‎src/backend/parser/gram.y

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6434,6 +6434,32 @@ IndexStmt:CREATE opt_unique INDEX opt_concurrently opt_index_name
64346434
n->isconstraint =false;
64356435
n->deferrable =false;
64366436
n->initdeferred =false;
6437+
n->if_not_exists =false;
6438+
$$ = (Node *)n;
6439+
}
6440+
|CREATEopt_uniqueINDEXopt_concurrentlyIF_PNOTEXISTSindex_name
6441+
ONqualified_nameaccess_method_clause'('index_params')'
6442+
opt_reloptionsOptTableSpacewhere_clause
6443+
{
6444+
IndexStmt *n = makeNode(IndexStmt);
6445+
n->unique =$2;
6446+
n->concurrent =$4;
6447+
n->idxname =$8;
6448+
n->relation =$10;
6449+
n->accessMethod =$11;
6450+
n->indexParams =$13;
6451+
n->options =$15;
6452+
n->tableSpace =$16;
6453+
n->whereClause =$17;
6454+
n->excludeOpNames = NIL;
6455+
n->idxcomment =NULL;
6456+
n->indexOid = InvalidOid;
6457+
n->oldNode = InvalidOid;
6458+
n->primary =false;
6459+
n->isconstraint =false;
6460+
n->deferrable =false;
6461+
n->initdeferred =false;
6462+
n->if_not_exists =true;
64376463
$$ = (Node *)n;
64386464
}
64396465
;

‎src/include/catalog/index.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -60,7 +60,8 @@ extern Oid index_create(Relation heapRelation,
6060
boolallow_system_table_mods,
6161
boolskip_build,
6262
boolconcurrent,
63-
boolis_internal);
63+
boolis_internal,
64+
boolif_not_exists);
6465

6566
externvoidindex_constraint_create(RelationheapRelation,
6667
OidindexRelationId,

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2256,6 +2256,7 @@ typedef struct IndexStmt
22562256
booldeferrable;/* is the constraint DEFERRABLE? */
22572257
boolinitdeferred;/* is the constraint INITIALLY DEFERRED? */
22582258
boolconcurrent;/* should this be a concurrent index build? */
2259+
boolif_not_exists;/* just do nothing if index already exists? */
22592260
}IndexStmt;
22602261

22612262
/* ----------------------

‎src/test/regress/expected/create_index.out

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,12 @@
66
-- BTREE
77
--
88
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
9+
CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);
10+
NOTICE: relation "onek_unique1" already exists, skipping
11+
CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);
12+
ERROR: syntax error at or near "ON"
13+
LINE 1: CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_...
14+
^
915
CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
1016
CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
1117
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;
22902296
CREATE TABLE concur_heap (f1 text, f2 text);
22912297
-- empty table
22922298
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
2299+
CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1);
2300+
NOTICE: relation "concur_index1" already exists, skipping
22932301
INSERT INTO concur_heap VALUES ('a','b');
22942302
INSERT INTO concur_heap VALUES ('b','b');
22952303
-- unique index
22962304
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
2305+
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1);
2306+
NOTICE: relation "concur_index2" already exists, skipping
22972307
-- check if constraint is set up properly to be enforced
22982308
INSERT INTO concur_heap VALUES ('b','x');
22992309
ERROR: duplicate key value violates unique constraint "concur_index2"

‎src/test/regress/sql/create_index.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,10 @@
88
--
99
CREATEINDEXonek_unique1ON onek USING btree(unique1 int4_ops);
1010

11+
CREATEINDEXIF NOT EXISTS onek_unique1ON onek USING btree(unique1 int4_ops);
12+
13+
CREATEINDEXIF NOT EXISTSON onek USING btree(unique1 int4_ops);
14+
1115
CREATEINDEXonek_unique2ON onek USING btree(unique2 int4_ops);
1216

1317
CREATEINDEXonek_hundredON 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;
711715
CREATETABLEconcur_heap (f1text, f2text);
712716
-- empty table
713717
CREATEINDEXCONCURRENTLY concur_index1ON concur_heap(f2,f1);
718+
CREATEINDEXCONCURRENTLY IF NOT EXISTS concur_index1ON concur_heap(f2,f1);
714719
INSERT INTO concur_heapVALUES ('a','b');
715720
INSERT INTO concur_heapVALUES ('b','b');
716721
-- unique index
717722
CREATEUNIQUE INDEXCONCURRENTLY concur_index2ON concur_heap(f1);
723+
CREATEUNIQUE INDEXCONCURRENTLY IF NOT EXISTS concur_index2ON concur_heap(f1);
718724
-- check if constraint is set up properly to be enforced
719725
INSERT INTO concur_heapVALUES ('b','x');
720726
-- check if constraint is enforced properly at build time

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp