Skip to content

Commit 3151019

Browse files
tglsfdctglsfdc
committed
Minor corrections for ALTER TYPE ADD VALUE IF NOT EXISTS patch.
Produce a NOTICE when the label already exists, for consistency with other CREATE IF NOT EXISTS commands. Also, fix the code so it produces something more user-friendly than an index violation when the label already exists. This not incidentally enables making a regression test that the previous patch didn't make for fear of exposing an unpredictable OID in the results. Also some wordsmithing on the documentation.
1 parent fcc1576 commit 3151019

File tree

5 files changed

+33
-25
lines changed

5 files changed

+33
-25
lines changed

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

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -109,15 +109,16 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE [ IF NOT
109109
<term><literal>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</literal></term>
110110
<listitem>
111111
<para>
112-
This form adds a new value to an enum type. If the new value's place in
113-
the enum's ordering is not specified using <literal>BEFORE</literal> or
114-
<literal>AFTER</literal>, then the new item is placed at the end of the
115-
list of values.
112+
This form adds a new value to an enum type. The new value's place in
113+
the enum's ordering can be specified as being <literal>BEFORE</literal>
114+
or <literal>AFTER</literal> one of the existing values. Otherwise,
115+
the new item is added at the end of the list of values.
116116
</para>
117117
<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.
118+
If <literal>IF NOT EXISTS</literal> is specified, it is not an error if
119+
the type already contains the new value: a notice is issued but no other
120+
action is taken. Otherwise, an error will occur if the new value is
121+
already present.
121122
</para>
122123
</listitem>
123124
</varlistentry>

‎src/backend/catalog/pg_enum.c

Lines changed: 20 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -212,19 +212,30 @@ AddEnumLabel(Oid enumTypeOid,
212212
*/
213213
LockDatabaseObject(TypeRelationId, enumTypeOid, 0, ExclusiveLock);
214214

215-
/* Do the "IF NOT EXISTS" test if specified */
216-
if (skipIfExists)
215+
/*
216+
* Check if label is already in use. The unique index on pg_enum would
217+
* catch this anyway, but we prefer a friendlier error message, and
218+
* besides we need a check to support IF NOT EXISTS.
219+
*/
220+
enum_tup = SearchSysCache2(ENUMTYPOIDNAME,
221+
ObjectIdGetDatum(enumTypeOid),
222+
CStringGetDatum(newVal));
223+
if (HeapTupleIsValid(enum_tup))
217224
{
218-
HeapTuple tup;
219-
220-
tup = SearchSysCache2(ENUMTYPOIDNAME,
221-
ObjectIdGetDatum(enumTypeOid),
222-
CStringGetDatum(newVal));
223-
if (HeapTupleIsValid(tup))
225+
ReleaseSysCache(enum_tup);
226+
if (skipIfExists)
224227
{
225-
ReleaseSysCache(tup);
228+
ereport(NOTICE,
229+
(errcode(ERRCODE_DUPLICATE_OBJECT),
230+
errmsg("enum label \"%s\" already exists, skipping",
231+
newVal)));
226232
return;
227233
}
234+
else
235+
ereport(ERROR,
236+
(errcode(ERRCODE_DUPLICATE_OBJECT),
237+
errmsg("enum label \"%s\" already exists",
238+
newVal)));
228239
}
229240

230241
pg_enum = heap_open(EnumRelationId, RowExclusiveLock);

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2306,7 +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 */
2309+
bool skipIfExists; /* no error if label already exists */
23102310
} AlterEnumStmt;
23112311

23122312
/* ----------------------

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -97,11 +97,11 @@ ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
9797
ERROR: "zeus" is not an existing enum label
9898
-- if not exists tests
9999
-- 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';
100+
ALTER TYPE planets ADD VALUE 'mercury';
101+
ERROR: enum label "mercury" already exists
103102
-- unless IF NOT EXISTS is specified
104103
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
104+
NOTICE: enum label "mercury" already exists, skipping
105105
-- should be neptune, not mercury
106106
SELECT enum_last(NULL::planets);
107107
enum_last

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

Lines changed: 1 addition & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -57,10 +57,7 @@ ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
5757
-- if not exists tests
5858

5959
-- 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';
60+
ALTER TYPE planets ADD VALUE 'mercury';
6461

6562
-- unless IF NOT EXISTS is specified
6663
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
@@ -73,7 +70,6 @@ ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
7370
-- should be pluto, i.e. the new value
7471
SELECT enum_last(NULL::planets);
7572

76-
7773
--
7874
-- Test inserting so many values that we have to renumber
7975
--

0 commit comments

Comments
 (0)