Skip to content

Commit 6d12b68

Browse files
adunstanadunstan
committed
Allow IF NOT EXISTS when add a new enum label.
If the label is already in the enum the statement becomes a no-op. This will reduce the pain that comes from our not allowing this operation inside a transaction block. Andrew Dunstan, reviewed by Tom Lane and Magnus Hagander.
1 parent 11e1318 commit 6d12b68

File tree

10 files changed

+89
-14
lines changed

10 files changed

+89
-14
lines changed

‎doc/src/sgml/ref/alter_type.sgml

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceab
2828
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME ATTRIBUTE <replaceable class="PARAMETER">attribute_name</replaceable> TO <replaceable class="PARAMETER">new_attribute_name</replaceable>
2929
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> [ CASCADE | RESTRICT ]
3030
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
31-
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replaceable class="PARAMETER">new_enum_value</replaceable> [ { BEFORE | AFTER } <replaceable class="PARAMETER">existing_enum_value</replaceable> ]
31+
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE [ IF NOT EXISTS ] <replaceable class="PARAMETER">new_enum_value</replaceable> [ { BEFORE | AFTER } <replaceable class="PARAMETER">existing_enum_value</replaceable> ]
3232

3333
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3434

@@ -106,14 +106,19 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replacea
106106
</varlistentry>
107107

108108
<varlistentry>
109-
<term><literal>ADD VALUE [ BEFORE | AFTER ]</literal></term>
109+
<term><literal>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</literal></term>
110110
<listitem>
111111
<para>
112112
This form adds a new value to an enum type. If the new value's place in
113113
the enum's ordering is not specified using <literal>BEFORE</literal> or
114114
<literal>AFTER</literal>, then the new item is placed at the end of the
115115
list of values.
116116
</para>
117+
<para>
118+
If <literal>IF NOT EXISTS</literal is used, it is not an error if the
119+
type already contains the new value, and no action is taken. Otherwise,
120+
an error will occur if the new value is already present.
121+
</para>
117122
</listitem>
118123
</varlistentry>
119124

‎src/backend/catalog/pg_enum.c

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -179,7 +179,8 @@ void
179179
AddEnumLabel(Oid enumTypeOid,
180180
const char *newVal,
181181
const char *neighbor,
182-
bool newValIsAfter)
182+
bool newValIsAfter,
183+
bool skipIfExists)
183184
{
184185
Relation pg_enum;
185186
Oid newOid;
@@ -211,6 +212,21 @@ AddEnumLabel(Oid enumTypeOid,
211212
*/
212213
LockDatabaseObject(TypeRelationId, enumTypeOid, 0, ExclusiveLock);
213214

215+
/* Do the "IF NOT EXISTS" test if specified */
216+
if (skipIfExists)
217+
{
218+
HeapTuple tup;
219+
220+
tup = SearchSysCache2(ENUMTYPOIDNAME,
221+
ObjectIdGetDatum(enumTypeOid),
222+
CStringGetDatum(newVal));
223+
if (HeapTupleIsValid(tup))
224+
{
225+
ReleaseSysCache(tup);
226+
return;
227+
}
228+
}
229+
214230
pg_enum = heap_open(EnumRelationId, RowExclusiveLock);
215231

216232
/* If we have to renumber the existing members, we restart from here */

‎src/backend/commands/typecmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1188,7 +1188,8 @@ AlterEnum(AlterEnumStmt *stmt)
11881188

11891189
/* Add the new label */
11901190
AddEnumLabel(enum_type_oid, stmt->newVal,
1191-
stmt->newValNeighbor, stmt->newValIsAfter);
1191+
stmt->newValNeighbor, stmt->newValIsAfter,
1192+
stmt->skipIfExists);
11921193

11931194
ReleaseSysCache(tup);
11941195
}

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3055,6 +3055,7 @@ _copyAlterEnumStmt(const AlterEnumStmt *from)
30553055
COPY_STRING_FIELD(newVal);
30563056
COPY_STRING_FIELD(newValNeighbor);
30573057
COPY_SCALAR_FIELD(newValIsAfter);
3058+
COPY_SCALAR_FIELD(skipIfExists);
30583059

30593060
return newnode;
30603061
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1439,6 +1439,7 @@ _equalAlterEnumStmt(const AlterEnumStmt *a, const AlterEnumStmt *b)
14391439
COMPARE_STRING_FIELD(newVal);
14401440
COMPARE_STRING_FIELD(newValNeighbor);
14411441
COMPARE_SCALAR_FIELD(newValIsAfter);
1442+
COMPARE_SCALAR_FIELD(skipIfExists);
14421443

14431444
return true;
14441445
}

‎src/backend/parser/gram.y

Lines changed: 16 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -470,7 +470,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
470470
%type <windef> window_definition over_clause window_specification
471471
opt_frame_clause frame_extent frame_bound
472472
%type <str> opt_existing_window_name
473-
473+
%type <boolean> opt_if_not_exists
474474

475475
/*
476476
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -4618,35 +4618,42 @@ enum_val_list: Sconst
46184618
*****************************************************************************/
46194619

46204620
AlterEnumStmt:
4621-
ALTER TYPE_P any_name ADD_P VALUE_P Sconst
4621+
ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst
46224622
{
46234623
AlterEnumStmt *n = makeNode(AlterEnumStmt);
46244624
n->typeName = $3;
4625-
n->newVal = $6;
4625+
n->newVal = $7;
46264626
n->newValNeighbor = NULL;
46274627
n->newValIsAfter = true;
4628+
n->skipIfExists = $6;
46284629
$$ = (Node *) n;
46294630
}
4630-
| ALTER TYPE_P any_name ADD_P VALUE_P Sconst BEFORE Sconst
4631+
| ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst BEFORE Sconst
46314632
{
46324633
AlterEnumStmt *n = makeNode(AlterEnumStmt);
46334634
n->typeName = $3;
4634-
n->newVal = $6;
4635-
n->newValNeighbor = $8;
4635+
n->newVal = $7;
4636+
n->newValNeighbor = $9;
46364637
n->newValIsAfter = false;
4638+
n->skipIfExists = $6;
46374639
$$ = (Node *) n;
46384640
}
4639-
| ALTER TYPE_P any_name ADD_P VALUE_P Sconst AFTER Sconst
4641+
| ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst AFTER Sconst
46404642
{
46414643
AlterEnumStmt *n = makeNode(AlterEnumStmt);
46424644
n->typeName = $3;
4643-
n->newVal = $6;
4644-
n->newValNeighbor = $8;
4645+
n->newVal = $7;
4646+
n->newValNeighbor = $9;
46454647
n->newValIsAfter = true;
4648+
n->skipIfExists = $6;
46464649
$$ = (Node *) n;
46474650
}
46484651
;
46494652

4653+
opt_if_not_exists: IF_P NOT EXISTS { $$ = true; }
4654+
| /* empty */ { $$ = false; }
4655+
;
4656+
46504657

46514658
/*****************************************************************************
46524659
*

‎src/include/catalog/pg_enum.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,7 @@ typedef FormData_pg_enum *Form_pg_enum;
6565
extern void EnumValuesCreate(Oid enumTypeOid, List *vals);
6666
extern void EnumValuesDelete(Oid enumTypeOid);
6767
extern void AddEnumLabel(Oid enumTypeOid, const char *newVal,
68-
const char *neighbor, bool newValIsAfter);
68+
const char *neighbor, bool newValIsAfter,
69+
bool skipIfExists);
6970

7071
#endif /* PG_ENUM_H */

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2306,6 +2306,7 @@ typedef struct AlterEnumStmt
23062306
char *newVal; /* new enum value's name */
23072307
char *newValNeighbor; /* neighboring enum value, if specified */
23082308
bool newValIsAfter; /* place new enum value after neighbor? */
2309+
bool skipIfExists; /* ignore statement if label already exists */
23092310
} AlterEnumStmt;
23102311

23112312
/* ----------------------

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,28 @@ ERROR: invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutopl
9595
DETAIL: Labels must be 63 characters or less.
9696
ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
9797
ERROR: "zeus" is not an existing enum label
98+
-- if not exists tests
99+
-- existing value gives error
100+
-- We can't do this test because the error contains the
101+
-- offending Oid value, which is unpredictable.
102+
-- ALTER TYPE planets ADD VALUE 'mercury';
103+
-- unless IF NOT EXISTS is specified
104+
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
105+
-- should be neptune, not mercury
106+
SELECT enum_last(NULL::planets);
107+
enum_last
108+
-----------
109+
neptune
110+
(1 row)
111+
112+
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
113+
-- should be pluto, i.e. the new value
114+
SELECT enum_last(NULL::planets);
115+
enum_last
116+
-----------
117+
pluto
118+
(1 row)
119+
98120
--
99121
-- Test inserting so many values that we have to renumber
100122
--

‎src/test/regress/sql/enum.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,26 @@ ALTER TYPE planets ADD VALUE
5454

5555
ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
5656

57+
-- if not exists tests
58+
59+
-- existing value gives error
60+
61+
-- We can't do this test because the error contains the
62+
-- offending Oid value, which is unpredictable.
63+
-- ALTER TYPE planets ADD VALUE 'mercury';
64+
65+
-- unless IF NOT EXISTS is specified
66+
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
67+
68+
-- should be neptune, not mercury
69+
SELECT enum_last(NULL::planets);
70+
71+
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
72+
73+
-- should be pluto, i.e. the new value
74+
SELECT enum_last(NULL::planets);
75+
76+
5777
--
5878
-- Test inserting so many values that we have to renumber
5979
--

0 commit comments

Comments
 (0)