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

Commit84c123b

Browse files
committed
Allow new values to be added to an existing enum type.
After much expenditure of effort, we've got this to the point where theperformance penalty is pretty minimal in typical cases.Andrew Dunstan, reviewed by Brendan Jurd, Dean Rasheed, and Tom Lane
1 parent24b29ca commit84c123b

File tree

23 files changed

+1423
-171
lines changed

23 files changed

+1423
-171
lines changed

‎contrib/pg_upgrade/function.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -79,7 +79,7 @@ install_support_functions(void)
7979
"LANGUAGE C STRICT;"));
8080
PQclear(executeQueryOrDie(conn,
8181
"CREATE OR REPLACE FUNCTION "
82-
"binary_upgrade.add_pg_enum_label(OID, OID, NAME) "
82+
"binary_upgrade.set_next_pg_enum_oid(OID) "
8383
"RETURNS VOID "
8484
"AS '$libdir/pg_upgrade_support' "
8585
"LANGUAGE C STRICT;"));

‎contrib/pg_upgrade_support/pg_upgrade_support.c

Lines changed: 5 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -16,13 +16,6 @@
1616

1717
/* THIS IS USED ONLY FOR PG >= 9.0 */
1818

19-
/*
20-
* Cannot include "catalog/pg_enum.h" here because we might
21-
* not be compiling against PG 9.0.
22-
*/
23-
externvoidEnumValuesCreate(OidenumTypeOid,List*vals,
24-
Oidbinary_upgrade_next_pg_enum_oid);
25-
2619
#ifdefPG_MODULE_MAGIC
2720
PG_MODULE_MAGIC;
2821
#endif
@@ -33,22 +26,23 @@ extern PGDLLIMPORT Oid binary_upgrade_next_pg_type_toast_oid;
3326
externPGDLLIMPORTOidbinary_upgrade_next_heap_relfilenode;
3427
externPGDLLIMPORTOidbinary_upgrade_next_toast_relfilenode;
3528
externPGDLLIMPORTOidbinary_upgrade_next_index_relfilenode;
29+
externPGDLLIMPORTOidbinary_upgrade_next_pg_enum_oid;
3630

3731
Datumset_next_pg_type_oid(PG_FUNCTION_ARGS);
3832
Datumset_next_pg_type_array_oid(PG_FUNCTION_ARGS);
3933
Datumset_next_pg_type_toast_oid(PG_FUNCTION_ARGS);
4034
Datumset_next_heap_relfilenode(PG_FUNCTION_ARGS);
4135
Datumset_next_toast_relfilenode(PG_FUNCTION_ARGS);
4236
Datumset_next_index_relfilenode(PG_FUNCTION_ARGS);
43-
Datumadd_pg_enum_label(PG_FUNCTION_ARGS);
37+
Datumset_next_pg_enum_oid(PG_FUNCTION_ARGS);
4438

4539
PG_FUNCTION_INFO_V1(set_next_pg_type_oid);
4640
PG_FUNCTION_INFO_V1(set_next_pg_type_array_oid);
4741
PG_FUNCTION_INFO_V1(set_next_pg_type_toast_oid);
4842
PG_FUNCTION_INFO_V1(set_next_heap_relfilenode);
4943
PG_FUNCTION_INFO_V1(set_next_toast_relfilenode);
5044
PG_FUNCTION_INFO_V1(set_next_index_relfilenode);
51-
PG_FUNCTION_INFO_V1(add_pg_enum_label);
45+
PG_FUNCTION_INFO_V1(set_next_pg_enum_oid);
5246

5347
Datum
5448
set_next_pg_type_oid(PG_FUNCTION_ARGS)
@@ -111,14 +105,11 @@ set_next_index_relfilenode(PG_FUNCTION_ARGS)
111105
}
112106

113107
Datum
114-
add_pg_enum_label(PG_FUNCTION_ARGS)
108+
set_next_pg_enum_oid(PG_FUNCTION_ARGS)
115109
{
116110
Oidenumoid=PG_GETARG_OID(0);
117-
Oidtypoid=PG_GETARG_OID(1);
118-
Namelabel=PG_GETARG_NAME(2);
119111

120-
EnumValuesCreate(typoid,list_make1(makeString(NameStr(*label))),
121-
enumoid);
112+
binary_upgrade_next_pg_enum_oid=enumoid;
122113

123114
PG_RETURN_VOID();
124115
}

‎doc/src/sgml/catalogs.sgml

Lines changed: 29 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2623,12 +2623,9 @@
26232623

26242624
<para>
26252625
The <structname>pg_enum</structname> catalog contains entries
2626-
matching enum types to their associated values and labels. The
2626+
showing the values and labels for each enum type. The
26272627
internal representation of a given enum value is actually the OID
2628-
of its associated row in <structname>pg_enum</structname>. The
2629-
OIDs for a particular enum type are guaranteed to be ordered in
2630-
the way the type should sort, but there is no guarantee about the
2631-
ordering of OIDs of unrelated enum types.
2628+
of its associated row in <structname>pg_enum</structname>.
26322629
</para>
26332630

26342631
<table>
@@ -2652,6 +2649,13 @@
26522649
<entry>The OID of the <structname>pg_type</> entry owning this enum value</entry>
26532650
</row>
26542651

2652+
<row>
2653+
<entry><structfield>enumsortorder</structfield></entry>
2654+
<entry><type>float4</type></entry>
2655+
<entry></entry>
2656+
<entry>The sort position of this enum value within its enum type</entry>
2657+
</row>
2658+
26552659
<row>
26562660
<entry><structfield>enumlabel</structfield></entry>
26572661
<entry><type>name</type></entry>
@@ -2661,6 +2665,26 @@
26612665
</tbody>
26622666
</tgroup>
26632667
</table>
2668+
2669+
<para>
2670+
The OIDs for <structname>pg_enum</structname> rows follow a special
2671+
rule: even-numbered OIDs are guaranteed to be ordered in the same way
2672+
as the sort ordering of their enum type. That is, if two even OIDs
2673+
belong to the same enum type, the smaller OID must have the smaller
2674+
<structfield>enumsortorder</structfield> value. Odd-numbered OID values
2675+
need bear no relationship to the sort order. This rule allows the
2676+
enum comparison routines to avoid catalog lookups in many common cases.
2677+
The routines that create and alter enum types attempt to assign even
2678+
OIDs to enum values whenever possible.
2679+
</para>
2680+
2681+
<para>
2682+
When an enum type is created, its members are assigned sort-order
2683+
positions 1..<replaceable>n</>. But members added later might be given
2684+
negative or fractional values of <structfield>enumsortorder</structfield>.
2685+
The only requirement on these values is that they be correctly
2686+
ordered and unique within each enum type.
2687+
</para>
26642688
</sect1>
26652689

26662690

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

Lines changed: 65 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -24,10 +24,11 @@ PostgreSQL documentation
2424
<refsynopsisdiv>
2525
<synopsis>
2626
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> <replaceable class="PARAMETER">action</replaceable> [, ... ]
27-
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
27+
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
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>
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 <replaceable class="PARAMETER">new_enum_value</replaceable> [ { BEFORE | AFTER } <replaceable class="PARAMETER">existing_enum_value</replaceable> ]
3132

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

@@ -103,6 +104,18 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replace
103104
</para>
104105
</listitem>
105106
</varlistentry>
107+
108+
<varlistentry>
109+
<term><literal>ADD [ BEFORE | AFTER ]</literal></term>
110+
<listitem>
111+
<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.
116+
</para>
117+
</listitem>
118+
</varlistentry>
106119
</variablelist>
107120
</para>
108121

@@ -181,7 +194,7 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replace
181194
<term><replaceable class="PARAMETER">new_attribute_name</replaceable></term>
182195
<listitem>
183196
<para>
184-
The new name of the attributebegin renamed.
197+
The new name of the attributeto be renamed.
185198
</para>
186199
</listitem>
187200
</varlistentry>
@@ -196,10 +209,53 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replace
196209
</listitem>
197210
</varlistentry>
198211

212+
<varlistentry>
213+
<term><replaceable class="PARAMETER">new_enum_value</replaceable></term>
214+
<listitem>
215+
<para>
216+
The new value to be added to an enum type's list of values.
217+
Like all enum literals, it needs to be quoted.
218+
</para>
219+
</listitem>
220+
</varlistentry>
221+
222+
<varlistentry>
223+
<term><replaceable class="PARAMETER">existing_enum_value</replaceable></term>
224+
<listitem>
225+
<para>
226+
The existing enum value that the new value should be added immediately
227+
before or after in the enum type's sort ordering.
228+
Like all enum literals, it needs to be quoted.
229+
</para>
230+
</listitem>
231+
</varlistentry>
232+
199233
</variablelist>
200234
</para>
201235
</refsect1>
202236

237+
<refsect1>
238+
<title>Notes</title>
239+
240+
<para>
241+
<command>ALTER TYPE ... ADD</> (the form that adds a new value to an
242+
enum type) cannot be executed inside a transaction block.
243+
</para>
244+
245+
<para>
246+
Comparisons involving an added enum value will sometimes be slower than
247+
comparisons involving only original members of the enum type. This will
248+
usually only occur if <literal>BEFORE</literal> or <literal>AFTER</literal>
249+
is used to set the new value's sort position somewhere other than at the
250+
end of the list. However, sometimes it will happen even though the new
251+
value is added at the end (this occurs if the OID counter <quote>wrapped
252+
around</> since the original creation of the enum type). The slowdown is
253+
usually insignificant; but if it matters, optimal performance can be
254+
regained by dropping and recreating the enum type, or by dumping and
255+
reloading the database.
256+
</para>
257+
</refsect1>
258+
203259
<refsect1>
204260
<title>Examples</title>
205261

@@ -230,6 +286,13 @@ ALTER TYPE email SET SCHEMA customers;
230286
To add a new attribute to a type:
231287
<programlisting>
232288
ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
289+
</programlisting>
290+
</para>
291+
292+
<para>
293+
To add a new value to an enum type in a particular sort position:
294+
<programlisting>
295+
ALTER TYPE colors ADD 'orange' AFTER 'red';
233296
</programlisting>
234297
</para>
235298
</refsect1>

0 commit comments

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