Skip to content

Commit 0ab9c56

Browse files
tglsfdctglsfdc
committed
Support renaming an existing value of an enum type.
Not much to be said about this patch: it does what it says on the tin. In passing, rename AlterEnumStmt.skipIfExists to skipIfNewValExists to clarify what it actually does. In the discussion of this patch we considered supporting other similar options, such as IF EXISTS on the type as a whole or IF NOT EXISTS on the target name. This patch doesn't actually add any such feature, but it might happen later. Dagfinn Ilmari Mannsåker, reviewed by Emre Hasegeli Discussion: <CAO=2mx6uvgPaPDf-rHqG8=1MZnGyVDMQeh8zS4euRyyg4D35OQ@mail.gmail.com>
1 parent bd180b6 commit 0ab9c56

File tree

10 files changed

+208
-29
lines changed

10 files changed

+208
-29
lines changed

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

Lines changed: 51 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,8 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO { <replace
2828
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME ATTRIBUTE <replaceable class="PARAMETER">attribute_name</replaceable> TO <replaceable class="PARAMETER">new_attribute_name</replaceable> [ CASCADE | RESTRICT ]
2929
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
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 [ IF NOT EXISTS ] <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">neighbor_enum_value</replaceable> ]
32+
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME VALUE <replaceable class="PARAMETER">existing_enum_value</replaceable> TO <replaceable class="PARAMETER">new_enum_value</replaceable>
3233

3334
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3435

@@ -124,21 +125,13 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE [ IF NOT
124125
</varlistentry>
125126

126127
<varlistentry>
127-
<term><literal>CASCADE</literal></term>
128+
<term><literal>RENAME VALUE</literal></term>
128129
<listitem>
129130
<para>
130-
Automatically propagate the operation to typed tables of the
131-
type being altered, and their descendants.
132-
</para>
133-
</listitem>
134-
</varlistentry>
135-
136-
<varlistentry>
137-
<term><literal>RESTRICT</literal></term>
138-
<listitem>
139-
<para>
140-
Refuse the operation if the type being altered is the type of a
141-
typed table. This is the default.
131+
This form renames a value of an enum type.
132+
The value's place in the enum's ordering is not affected.
133+
An error will occur if the specified value is not present or the new
134+
name is already present.
142135
</para>
143136
</listitem>
144137
</varlistentry>
@@ -241,14 +234,15 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE [ IF NOT
241234
<term><replaceable class="PARAMETER">new_enum_value</replaceable></term>
242235
<listitem>
243236
<para>
244-
The new value to be added to an enum type's list of values.
237+
The new value to be added to an enum type's list of values,
238+
or the new name to be given to an existing value.
245239
Like all enum literals, it needs to be quoted.
246240
</para>
247241
</listitem>
248242
</varlistentry>
249243

250244
<varlistentry>
251-
<term><replaceable class="PARAMETER">existing_enum_value</replaceable></term>
245+
<term><replaceable class="PARAMETER">neighbor_enum_value</replaceable></term>
252246
<listitem>
253247
<para>
254248
The existing enum value that the new value should be added immediately
@@ -258,6 +252,36 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE [ IF NOT
258252
</listitem>
259253
</varlistentry>
260254

255+
<varlistentry>
256+
<term><replaceable class="PARAMETER">existing_enum_value</replaceable></term>
257+
<listitem>
258+
<para>
259+
The existing enum value that should be renamed.
260+
Like all enum literals, it needs to be quoted.
261+
</para>
262+
</listitem>
263+
</varlistentry>
264+
265+
<varlistentry>
266+
<term><literal>CASCADE</literal></term>
267+
<listitem>
268+
<para>
269+
Automatically propagate the operation to typed tables of the
270+
type being altered, and their descendants.
271+
</para>
272+
</listitem>
273+
</varlistentry>
274+
275+
<varlistentry>
276+
<term><literal>RESTRICT</literal></term>
277+
<listitem>
278+
<para>
279+
Refuse the operation if the type being altered is the type of a
280+
typed table. This is the default.
281+
</para>
282+
</listitem>
283+
</varlistentry>
284+
261285
</variablelist>
262286
</para>
263287
</refsect1>
@@ -270,6 +294,8 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE [ IF NOT
270294
an enum type) is executed inside a transaction block, the new value cannot
271295
be used until after the transaction has been committed, except in the case
272296
that the enum type itself was created earlier in the same transaction.
297+
Likewise, when a pre-existing enum value is renamed, the transaction must
298+
be committed before the renamed value can be used.
273299
</para>
274300

275301
<para>
@@ -323,7 +349,15 @@ ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
323349
To add a new value to an enum type in a particular sort position:
324350
<programlisting>
325351
ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
326-
</programlisting></para>
352+
</programlisting>
353+
</para>
354+
355+
<para>
356+
To rename an enum value:
357+
<programlisting>
358+
ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
359+
</programlisting>
360+
</para>
327361
</refsect1>
328362

329363
<refsect1>

‎src/backend/catalog/pg_enum.c

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -465,6 +465,91 @@ AddEnumLabel(Oid enumTypeOid,
465465
}
466466

467467

468+
/*
469+
* RenameEnumLabel
470+
* Rename a label in an enum set.
471+
*/
472+
void
473+
RenameEnumLabel(Oid enumTypeOid,
474+
const char *oldVal,
475+
const char *newVal)
476+
{
477+
Relation pg_enum;
478+
HeapTuple enum_tup;
479+
Form_pg_enum en;
480+
CatCList *list;
481+
int nelems;
482+
HeapTuple old_tup;
483+
bool found_new;
484+
int i;
485+
486+
/* check length of new label is ok */
487+
if (strlen(newVal) > (NAMEDATALEN - 1))
488+
ereport(ERROR,
489+
(errcode(ERRCODE_INVALID_NAME),
490+
errmsg("invalid enum label \"%s\"", newVal),
491+
errdetail("Labels must be %d characters or less.",
492+
NAMEDATALEN - 1)));
493+
494+
/*
495+
* Acquire a lock on the enum type, which we won't release until commit.
496+
* This ensures that two backends aren't concurrently modifying the same
497+
* enum type. Since we are not changing the type's sort order, this is
498+
* probably not really necessary, but there seems no reason not to take
499+
* the lock to be sure.
500+
*/
501+
LockDatabaseObject(TypeRelationId, enumTypeOid, 0, ExclusiveLock);
502+
503+
pg_enum = heap_open(EnumRelationId, RowExclusiveLock);
504+
505+
/* Get the list of existing members of the enum */
506+
list = SearchSysCacheList1(ENUMTYPOIDNAME,
507+
ObjectIdGetDatum(enumTypeOid));
508+
nelems = list->n_members;
509+
510+
/*
511+
* Locate the element to rename and check if the new label is already in
512+
* use. (The unique index on pg_enum would catch that anyway, but we
513+
* prefer a friendlier error message.)
514+
*/
515+
old_tup = NULL;
516+
found_new = false;
517+
for (i = 0; i < nelems; i++)
518+
{
519+
enum_tup = &(list->members[i]->tuple);
520+
en = (Form_pg_enum) GETSTRUCT(enum_tup);
521+
if (strcmp(NameStr(en->enumlabel), oldVal) == 0)
522+
old_tup = enum_tup;
523+
if (strcmp(NameStr(en->enumlabel), newVal) == 0)
524+
found_new = true;
525+
}
526+
if (!old_tup)
527+
ereport(ERROR,
528+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
529+
errmsg("\"%s\" is not an existing enum label",
530+
oldVal)));
531+
if (found_new)
532+
ereport(ERROR,
533+
(errcode(ERRCODE_DUPLICATE_OBJECT),
534+
errmsg("enum label \"%s\" already exists",
535+
newVal)));
536+
537+
/* OK, make a writable copy of old tuple */
538+
enum_tup = heap_copytuple(old_tup);
539+
en = (Form_pg_enum) GETSTRUCT(enum_tup);
540+
541+
ReleaseCatCacheList(list);
542+
543+
/* Update the pg_enum entry */
544+
namestrcpy(&en->enumlabel, newVal);
545+
simple_heap_update(pg_enum, &enum_tup->t_self, enum_tup);
546+
CatalogUpdateIndexes(pg_enum, enum_tup);
547+
heap_freetuple(enum_tup);
548+
549+
heap_close(pg_enum, RowExclusiveLock);
550+
}
551+
552+
468553
/*
469554
* RenumberEnumType
470555
* Renumber existing enum elements to have sort positions 1..n.

‎src/backend/commands/typecmds.c

Lines changed: 14 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1241,17 +1241,25 @@ AlterEnum(AlterEnumStmt *stmt)
12411241
/* Check it's an enum and check user has permission to ALTER the enum */
12421242
checkEnumOwner(tup);
12431243

1244-
/* Add the new label */
1245-
AddEnumLabel(enum_type_oid, stmt->newVal,
1246-
stmt->newValNeighbor, stmt->newValIsAfter,
1247-
stmt->skipIfExists);
1244+
ReleaseSysCache(tup);
1245+
1246+
if (stmt->oldVal)
1247+
{
1248+
/* Rename an existing label */
1249+
RenameEnumLabel(enum_type_oid, stmt->oldVal, stmt->newVal);
1250+
}
1251+
else
1252+
{
1253+
/* Add a new label */
1254+
AddEnumLabel(enum_type_oid, stmt->newVal,
1255+
stmt->newValNeighbor, stmt->newValIsAfter,
1256+
stmt->skipIfNewValExists);
1257+
}
12481258

12491259
InvokeObjectPostAlterHook(TypeRelationId, enum_type_oid, 0);
12501260

12511261
ObjectAddressSet(address, TypeRelationId, enum_type_oid);
12521262

1253-
ReleaseSysCache(tup);
1254-
12551263
return address;
12561264
}
12571265

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3375,10 +3375,11 @@ _copyAlterEnumStmt(const AlterEnumStmt *from)
33753375
AlterEnumStmt *newnode = makeNode(AlterEnumStmt);
33763376

33773377
COPY_NODE_FIELD(typeName);
3378+
COPY_STRING_FIELD(oldVal);
33783379
COPY_STRING_FIELD(newVal);
33793380
COPY_STRING_FIELD(newValNeighbor);
33803381
COPY_SCALAR_FIELD(newValIsAfter);
3381-
COPY_SCALAR_FIELD(skipIfExists);
3382+
COPY_SCALAR_FIELD(skipIfNewValExists);
33823383

33833384
return newnode;
33843385
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1465,10 +1465,11 @@ static bool
14651465
_equalAlterEnumStmt(const AlterEnumStmt *a, const AlterEnumStmt *b)
14661466
{
14671467
COMPARE_NODE_FIELD(typeName);
1468+
COMPARE_STRING_FIELD(oldVal);
14681469
COMPARE_STRING_FIELD(newVal);
14691470
COMPARE_STRING_FIELD(newValNeighbor);
14701471
COMPARE_SCALAR_FIELD(newValIsAfter);
1471-
COMPARE_SCALAR_FIELD(skipIfExists);
1472+
COMPARE_SCALAR_FIELD(skipIfNewValExists);
14721473

14731474
return true;
14741475
}

‎src/backend/parser/gram.y

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5257,30 +5257,44 @@ AlterEnumStmt:
52575257
{
52585258
AlterEnumStmt *n = makeNode(AlterEnumStmt);
52595259
n->typeName = $3;
5260+
n->oldVal = NULL;
52605261
n->newVal = $7;
52615262
n->newValNeighbor = NULL;
52625263
n->newValIsAfter = true;
5263-
n->skipIfExists = $6;
5264+
n->skipIfNewValExists = $6;
52645265
$$ = (Node *) n;
52655266
}
52665267
| ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst BEFORE Sconst
52675268
{
52685269
AlterEnumStmt *n = makeNode(AlterEnumStmt);
52695270
n->typeName = $3;
5271+
n->oldVal = NULL;
52705272
n->newVal = $7;
52715273
n->newValNeighbor = $9;
52725274
n->newValIsAfter = false;
5273-
n->skipIfExists = $6;
5275+
n->skipIfNewValExists = $6;
52745276
$$ = (Node *) n;
52755277
}
52765278
| ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst AFTER Sconst
52775279
{
52785280
AlterEnumStmt *n = makeNode(AlterEnumStmt);
52795281
n->typeName = $3;
5282+
n->oldVal = NULL;
52805283
n->newVal = $7;
52815284
n->newValNeighbor = $9;
52825285
n->newValIsAfter = true;
5283-
n->skipIfExists = $6;
5286+
n->skipIfNewValExists = $6;
5287+
$$ = (Node *) n;
5288+
}
5289+
| ALTER TYPE_P any_name RENAME VALUE_P Sconst TO Sconst
5290+
{
5291+
AlterEnumStmt *n = makeNode(AlterEnumStmt);
5292+
n->typeName = $3;
5293+
n->oldVal = $6;
5294+
n->newVal = $8;
5295+
n->newValNeighbor = NULL;
5296+
n->newValIsAfter = false;
5297+
n->skipIfNewValExists = false;
52845298
$$ = (Node *) n;
52855299
}
52865300
;

‎src/include/catalog/pg_enum.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -67,5 +67,7 @@ extern void EnumValuesDelete(Oid enumTypeOid);
6767
extern void AddEnumLabel(Oid enumTypeOid, const char *newVal,
6868
const char *neighbor, bool newValIsAfter,
6969
bool skipIfExists);
70+
extern void RenameEnumLabel(Oid enumTypeOid,
71+
const char *oldVal, const char *newVal);
7072

7173
#endif /* PG_ENUM_H */

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2708,10 +2708,11 @@ typedef struct AlterEnumStmt
27082708
{
27092709
NodeTag type;
27102710
List *typeName; /* qualified name (list of Value strings) */
2711+
char *oldVal; /* old enum value's name, if renaming */
27112712
char *newVal; /* new enum value's name */
27122713
char *newValNeighbor; /* neighboring enum value, if specified */
27132714
bool newValIsAfter; /* place new enum value after neighbor? */
2714-
bool skipIfExists; /* no error if label already exists */
2715+
bool skipIfNewValExists; /* no error if new already exists? */
27152716
} AlterEnumStmt;
27162717

27172718
/* ----------------------

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -556,6 +556,28 @@ CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent);
556556
ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented
557557
DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow.
558558
DROP TYPE bogus;
559+
-- check renaming a value
560+
ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
561+
SELECT enumlabel, enumsortorder
562+
FROM pg_enum
563+
WHERE enumtypid = 'rainbow'::regtype
564+
ORDER BY 2;
565+
enumlabel | enumsortorder
566+
-----------+---------------
567+
crimson | 1
568+
orange | 2
569+
yellow | 3
570+
green | 4
571+
blue | 5
572+
purple | 6
573+
(6 rows)
574+
575+
-- check that renaming a non-existent value fails
576+
ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
577+
ERROR: "red" is not an existing enum label
578+
-- check that renaming to an existent value fails
579+
ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
580+
ERROR: enum label "green" already exists
559581
--
560582
-- check transactional behaviour of ALTER TYPE ... ADD VALUE
561583
--

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -257,6 +257,17 @@ CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly');
257257
CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent);
258258
DROP TYPE bogus;
259259

260+
-- check renaming a value
261+
ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
262+
SELECT enumlabel, enumsortorder
263+
FROM pg_enum
264+
WHERE enumtypid = 'rainbow'::regtype
265+
ORDER BY 2;
266+
-- check that renaming a non-existent value fails
267+
ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
268+
-- check that renaming to an existent value fails
269+
ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
270+
260271
--
261272
-- check transactional behaviour of ALTER TYPE ... ADD VALUE
262273
--

0 commit comments

Comments
 (0)