*** a/contrib/pg_upgrade/function.c --- b/contrib/pg_upgrade/function.c *************** *** 61,85 **** install_support_functions(migratorContext *ctx) "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_heap_relfilenode(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_toast_relfilenode(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_index_relfilenode(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.add_pg_enum_label(OID, OID, NAME) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); --- 61,85 ---- "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_pg_enum_oid(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_heap_relfilenode(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_toast_relfilenode(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); PQclear(executeQueryOrDie(ctx, conn, "CREATE OR REPLACE FUNCTION " ! " binary_upgrade.set_next_index_relfilenode(OID) " "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); *** a/contrib/pg_upgrade_support/pg_upgrade_support.c --- b/contrib/pg_upgrade_support/pg_upgrade_support.c *************** *** 30,35 **** PG_MODULE_MAGIC; --- 30,36 ---- extern PGDLLIMPORT Oid binary_upgrade_next_pg_type_oid; extern PGDLLIMPORT Oid binary_upgrade_next_pg_type_array_oid; extern PGDLLIMPORT Oid binary_upgrade_next_pg_type_toast_oid; + extern PGDLLIMPORT Oid binary_upgrade_next_pg_enum_oid; extern PGDLLIMPORT Oid binary_upgrade_next_heap_relfilenode; extern PGDLLIMPORT Oid binary_upgrade_next_toast_relfilenode; extern PGDLLIMPORT Oid binary_upgrade_next_index_relfilenode; *************** *** 37,54 **** extern PGDLLIMPORT Oid binary_upgrade_next_index_relfilenode; Datum set_next_pg_type_oid(PG_FUNCTION_ARGS); Datum set_next_pg_type_array_oid(PG_FUNCTION_ARGS); Datum set_next_pg_type_toast_oid(PG_FUNCTION_ARGS); Datum set_next_heap_relfilenode(PG_FUNCTION_ARGS); Datum set_next_toast_relfilenode(PG_FUNCTION_ARGS); Datum set_next_index_relfilenode(PG_FUNCTION_ARGS); - Datum add_pg_enum_label(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(set_next_pg_type_oid); PG_FUNCTION_INFO_V1(set_next_pg_type_array_oid); PG_FUNCTION_INFO_V1(set_next_pg_type_toast_oid); PG_FUNCTION_INFO_V1(set_next_heap_relfilenode); PG_FUNCTION_INFO_V1(set_next_toast_relfilenode); PG_FUNCTION_INFO_V1(set_next_index_relfilenode); - PG_FUNCTION_INFO_V1(add_pg_enum_label); Datum set_next_pg_type_oid(PG_FUNCTION_ARGS) --- 38,55 ---- Datum set_next_pg_type_oid(PG_FUNCTION_ARGS); Datum set_next_pg_type_array_oid(PG_FUNCTION_ARGS); Datum set_next_pg_type_toast_oid(PG_FUNCTION_ARGS); + Datum set_next_pg_enum_oid(PG_FUNCTION_ARGS); Datum set_next_heap_relfilenode(PG_FUNCTION_ARGS); Datum set_next_toast_relfilenode(PG_FUNCTION_ARGS); Datum set_next_index_relfilenode(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(set_next_pg_type_oid); PG_FUNCTION_INFO_V1(set_next_pg_type_array_oid); PG_FUNCTION_INFO_V1(set_next_pg_type_toast_oid); + PG_FUNCTION_INFO_V1(set_next_pg_enum_oid); PG_FUNCTION_INFO_V1(set_next_heap_relfilenode); PG_FUNCTION_INFO_V1(set_next_toast_relfilenode); PG_FUNCTION_INFO_V1(set_next_index_relfilenode); Datum set_next_pg_type_oid(PG_FUNCTION_ARGS) *************** *** 81,86 **** set_next_pg_type_toast_oid(PG_FUNCTION_ARGS) --- 82,97 ---- } Datum + set_next_pg_enum_oid(PG_FUNCTION_ARGS) + { + Oid enumoid = PG_GETARG_OID(0); + + binary_upgrade_next_pg_enum_oid = enumoid; + + PG_RETURN_VOID(); + } + + Datum set_next_heap_relfilenode(PG_FUNCTION_ARGS) { Oid relfilenode = PG_GETARG_OID(0); *************** *** 110,124 **** set_next_index_relfilenode(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } - Datum - add_pg_enum_label(PG_FUNCTION_ARGS) - { - Oid enumoid = PG_GETARG_OID(0); - Oid typoid = PG_GETARG_OID(1); - Name label = PG_GETARG_NAME(2); - - EnumValuesCreate(typoid, list_make1(makeString(NameStr(*label))), - enumoid); - - PG_RETURN_VOID(); - } --- 121,123 ---- *** a/doc/src/sgml/ref/alter_type.sgml --- b/doc/src/sgml/ref/alter_type.sgml *************** *** 23,33 **** PostgreSQL documentation ! ALTER TYPE name action [, ... ] ALTER TYPE name OWNER TO new_owner ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name ALTER TYPE name RENAME TO new_name ALTER TYPE name SET SCHEMA new_schema where action is one of: --- 23,34 ---- ! ALTER TYPE name action [, ... ] ALTER TYPE name OWNER TO new_owner ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name ALTER TYPE name RENAME TO new_name ALTER TYPE name SET SCHEMA new_schema + ALTER TYPE name ADD new_enum_value [ BEFORE | AFTER existing_enum_value where action is one of: *************** *** 103,108 **** ALTER TYPE name SET SCHEMA + + + ADD [ BEFORE | AFTER ] + + + This form adds a new value to an enum type. If the new value's place + in the sort order is not set using BEFORE or + AFTER, then the new item is placed at the end of + the list of values. + + + + Adding a new enum value will in some cases lower the comparison and + sorting performance of the enum type. This will only usually occur if + BEFORE or AFTER are used to set + the new value's sort order position somewhere other than at the end + of the list. Optimal performance will be restored if the database is + dumped and reloaded. + + + + *************** *** 196,201 **** ALTER TYPE name SET SCHEMA + + data_type + + + The data type of the attribute to add, or the new type of the + attribute to alter. + + + + + + new_enum_value + + + The new value to be added to the num type's list of values. Like all + enum literals it needs to be quoted. + + + + + + exisiting_enum_value + + + The neighbour of the new value to be added to the num type's list of values. Like all + enum literals it needs to be quoted. + + + + *************** *** 232,237 **** ALTER TYPE email SET SCHEMA customers; --- 285,297 ---- ALTER TYPE compfoo ADD ATTRIBUTE f3 int; + + + To add a new value to an enum type in a particular sort position: + + ALTER TYPE colors ADD 'orange' AFTER 'red'; + + *** a/src/backend/catalog/pg_enum.c --- b/src/backend/catalog/pg_enum.c *************** *** 18,29 **** --- 18,226 ---- #include "catalog/catalog.h" #include "catalog/indexing.h" #include "catalog/pg_enum.h" + #include "catalog/pg_type.h" + #include "utils/lsyscache.h" + #include "utils/syscache.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/rel.h" #include "utils/tqual.h" static int oid_cmp(const void *p1, const void *p2); + static int sort_order_cmp(const void *p1, const void *p2); + + Oid binary_upgrade_next_pg_enum_oid = InvalidOid; + + /* + * AddEnumLabel + * Add a new label to the enum set. By default it goes at + * the end, but the user can choose to place it before or + * after any existing set member. + * + * + */ + + void + AddEnumLabel(Oid enumTypeOid, + char *newVal, + char *neighbour, + bool newValIsAfter) + { + Oid newOid; + Relation pg_enum; + TupleDesc tupDesc; + Datum values[Natts_pg_enum]; + bool nulls[Natts_pg_enum]; + NameData enumlabel; + HeapTuple enum_tup; + int newelemorder; + CatCList *list; + int nelems; + + /* check length of new label is ok */ + if (strlen(newVal) > (NAMEDATALEN - 1)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_NAME), + errmsg("invalid enum label \"%s\"", newVal), + errdetail("Labels must be %d characters or less.", + NAMEDATALEN - 1))); + + /* get a new OID for the label */ + pg_enum = heap_open(EnumRelationId, RowExclusiveLock); + + list = SearchSysCacheList1(ENUMTYPOIDNAME, + ObjectIdGetDatum(enumTypeOid)); + nelems = list->n_members; + + if (neighbour == NULL) + { + /* + * Put the new label at the end of the list. + * No change to existing tuples is required. + */ + newelemorder = nelems + 1; + } + else + { + /* BEFORE or AFTER specified */ + int i; + HeapTuple *existing; + HeapTuple nbr = NULL; + Form_pg_enum nbr_en; + + /* sort the list of the existing elements by enumsortorder */ + existing = palloc(nelems * sizeof(HeapTuple)); + + for (i = 0; i < nelems; i++) + existing[i] = &(list->members[i]->tuple); + + qsort(existing, nelems, sizeof(HeapTuple), sort_order_cmp); + + /* locate the neighbour element */ + for (i = 0; i < nelems; i++) + { + Form_pg_enum exists_en; + exists_en = (Form_pg_enum) GETSTRUCT(existing[i]); + if (strcmp(NameStr(exists_en->enumlabel),neighbour) == 0) + nbr = existing[i]; + + } + + if (nbr == NULL) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" is not an existing label.", neighbour))); + } + + nbr_en = (Form_pg_enum) GETSTRUCT(nbr); + + /* + * If BEFORE was specified, the new label goes in the neighbour's + * position. Otherwise, it goes in the position after that. + */ + newelemorder = nbr_en->enumsortorder; + if (newValIsAfter) + newelemorder++; + + /* + * Add 1 to the sortorder of all the labels after where the + * new label goes. Do it from the end back so we don't get + * uniqueness violations. + */ + for (i = nelems - 1; i>= 0; i--) + { + HeapTuple newtup; + Form_pg_enum exst_en = (Form_pg_enum) GETSTRUCT(existing[i]); + if (exst_en->enumsortorder < newelemorder) + break; + + newtup = heap_copytuple(existing[i]); + exst_en = (Form_pg_enum) GETSTRUCT(newtup); + exst_en->enumsortorder ++; + + simple_heap_update(pg_enum, &newtup->t_self, newtup); + + CatalogUpdateIndexes(pg_enum, newtup); + + } + + } + + if (OidIsValid(binary_upgrade_next_pg_enum_oid)) + { + if (neighbour != NULL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("BEFORE/AFTER incompatible with binary upgrade."))); + + newOid = binary_upgrade_next_pg_enum_oid; + binary_upgrade_next_pg_enum_oid = InvalidOid; + } + else + { + /* + * Non upgrade case, we allocate a new Oid for the value. + * + * We try to give the new element an even numbered Oid if it's safe, + * which should be true if these conditions hold: + * . the Oid of the highest sorted exiting value is even, and + * . the new value is to sort after that value, and + * . there hasn't been Oid wraparound. + * For all other cases we allocate an odd Oid. + */ + + + newOid = GetNewOid(pg_enum); + if (newelemorder > nelems) + { + Oid last_elem_oid = InvalidOid; + int i; + + for (i = nelems - 1; i>= 0; i--) + { + Form_pg_enum last_en; + last_en = (Form_pg_enum) GETSTRUCT(&(list->members[i]->tuple)); + if (last_en->enumsortorder == nelems) + { + last_elem_oid = HeapTupleGetOid(&(list->members[i]->tuple)); + break; + } + } + + if (last_elem_oid %2 == 0) + while ((newOid %2 == 1 && newOid > last_elem_oid) || + (newOid %2 == 0 && newOid < last_elem_oid)) + newOid = GetNewOid(pg_enum); + else + while (newOid %2 == 0) + newOid = GetNewOid(pg_enum); + } + else + { + while (newOid %2 == 0) + newOid = GetNewOid(pg_enum); + } + } + + /* set up the new entry */ + tupDesc = pg_enum->rd_att; + memset(nulls, false, sizeof(nulls)); + values[Anum_pg_enum_enumtypid - 1] = ObjectIdGetDatum(enumTypeOid); + namestrcpy(&enumlabel, newVal); + values[Anum_pg_enum_enumlabel - 1] = NameGetDatum(&enumlabel); + values[Anum_pg_enum_enumsortorder -1] = Int32GetDatum(newelemorder); + enum_tup = heap_form_tuple(tupDesc, values, nulls); + HeapTupleSetOid(enum_tup, newOid); + simple_heap_insert(pg_enum, enum_tup); + CatalogUpdateIndexes(pg_enum, enum_tup); + heap_freetuple(enum_tup); + + heap_close(pg_enum, RowExclusiveLock); + + ReleaseCatCacheList(list); + + } /* *************** *** 33,40 **** static int oid_cmp(const void *p1, const void *p2); * vals is a list of Value strings. */ void ! EnumValuesCreate(Oid enumTypeOid, List *vals, ! Oid binary_upgrade_next_pg_enum_oid) { Relation pg_enum; TupleDesc tupDesc; --- 230,236 ---- * vals is a list of Value strings. */ void ! EnumValuesCreate(Oid enumTypeOid, List *vals) { Relation pg_enum; TupleDesc tupDesc; *************** *** 50,58 **** EnumValuesCreate(Oid enumTypeOid, List *vals, num_elems = list_length(vals); /* ! * XXX we do not bother to check the list of values for duplicates --- if * you have any, you'll get a less-than-friendly unique-index violation. ! * Is it worth trying harder? */ pg_enum = heap_open(EnumRelationId, RowExclusiveLock); --- 246,254 ---- num_elems = list_length(vals); /* ! * We do not bother to check the list of values for duplicates --- if * you have any, you'll get a less-than-friendly unique-index violation. ! * It is probably not worth trying harder. */ pg_enum = heap_open(EnumRelationId, RowExclusiveLock); *************** *** 62,96 **** EnumValuesCreate(Oid enumTypeOid, List *vals, * Allocate oids */ oids = (Oid *) palloc(num_elems * sizeof(Oid)); ! if (OidIsValid(binary_upgrade_next_pg_enum_oid)) ! { ! if (num_elems != 1) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("EnumValuesCreate() can only set a single OID"))); ! oids[0] = binary_upgrade_next_pg_enum_oid; ! binary_upgrade_next_pg_enum_oid = InvalidOid; ! } ! else { /* ! * While this method does not absolutely guarantee that we generate no ! * duplicate oids (since we haven't entered each oid into the table ! * before allocating the next), trouble could only occur if the oid ! * counter wraps all the way around before we finish. Which seems ! * unlikely. */ ! for (elemno = 0; elemno < num_elems; elemno++) ! { ! /* ! * The pg_enum.oid is stored in user tables. This oid must be ! * preserved by binary upgrades. ! */ ! oids[elemno] = GetNewOid(pg_enum); ! } ! /* sort them, just in case counter wrapped from high to low */ ! qsort(oids, num_elems, sizeof(Oid), oid_cmp); } /* and make the entries */ memset(nulls, false, sizeof(nulls)); --- 258,287 ---- * Allocate oids */ oids = (Oid *) palloc(num_elems * sizeof(Oid)); ! ! /* ! * While this method does not absolutely guarantee that we generate no ! * duplicate oids (since we haven't entered each oid into the table ! * before allocating the next), trouble could only occur if the oid ! * counter wraps all the way around before we finish. Which seems ! * unlikely. ! */ ! for (elemno = 0; elemno < num_elems; elemno++) { /* ! * The pg_enum.oid is stored in user tables. This oid must be ! * preserved by binary upgrades. ! * ! * We allocate all new enums as evenly numbered Oids ! * so they will sort fast. */ ! Oid new_oid; ! while ((new_oid = GetNewOid(pg_enum)) %2 == 1) ! ; ! oids[elemno] = new_oid; } + /* sort them, just in case counter wrapped from high to low */ + qsort(oids, num_elems, sizeof(Oid), oid_cmp); /* and make the entries */ memset(nulls, false, sizeof(nulls)); *************** *** 114,119 **** EnumValuesCreate(Oid enumTypeOid, List *vals, --- 305,311 ---- values[Anum_pg_enum_enumtypid - 1] = ObjectIdGetDatum(enumTypeOid); namestrcpy(&enumlabel, lab); values[Anum_pg_enum_enumlabel - 1] = NameGetDatum(&enumlabel); + values[Anum_pg_enum_enumsortorder -1] = Int32GetDatum(elemno+1); tup = heap_form_tuple(tupDesc, values, nulls); HeapTupleSetOid(tup, oids[elemno]); *************** *** 164,170 **** EnumValuesDelete(Oid enumTypeOid) } ! /* qsort comparison function */ static int oid_cmp(const void *p1, const void *p2) { --- 356,362 ---- } ! /* qsort comparison for oids */ static int oid_cmp(const void *p1, const void *p2) { *************** *** 177,179 **** oid_cmp(const void *p1, const void *p2) --- 369,384 ---- return 1; return 0; } + + /* qsort comparison function for tuples by sort order */ + static int + sort_order_cmp(const void *p1, const void *p2) + { + HeapTuple v1 = *((const HeapTuple *) p1); + HeapTuple v2 = *((const HeapTuple *) p2); + Form_pg_enum en1 = (Form_pg_enum) GETSTRUCT(v1); + Form_pg_enum en2 = (Form_pg_enum) GETSTRUCT(v2); + + return en1->enumsortorder - en2->enumsortorder; + } + *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** *** 85,96 **** static Oid findTypeTypmodoutFunction(List *procname); static Oid findTypeAnalyzeFunction(List *procname, Oid typeOid); static List *get_rels_with_domain(Oid domainOid, LOCKMODE lockmode); static void checkDomainOwner(HeapTuple tup, TypeName *typename); static char *domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, int typMod, Constraint *constr, char *domainName); - - /* * DefineType * Registers a new base type. --- 85,95 ---- static Oid findTypeAnalyzeFunction(List *procname, Oid typeOid); static List *get_rels_with_domain(Oid domainOid, LOCKMODE lockmode); static void checkDomainOwner(HeapTuple tup, TypeName *typename); + static void checkEnumOwner(HeapTuple tup, TypeName *typename); static char *domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, int typMod, Constraint *constr, char *domainName); /* * DefineType * Registers a new base type. *************** *** 563,569 **** DefineType(List *names, List *parameters) -1, /* typMod (Domains only) */ 0, /* Array Dimensions of typbasetype */ false); /* Type NOT NULL */ - /* * Create the array type that goes with it. */ --- 562,567 ---- *************** *** 1044,1050 **** DefineDomain(CreateDomainStmt *stmt) storage, /* TOAST strategy */ basetypeMod, /* typeMod value */ typNDims, /* Array dimensions for base type */ ! typNotNull); /* Type NOT NULL */ /* * Process constraints which refer to the domain ID returned by TypeCreate --- 1042,1048 ---- storage, /* TOAST strategy */ basetypeMod, /* typeMod value */ typNDims, /* Array dimensions for base type */ ! typNotNull); /* Type NOT NULL */ /* * Process constraints which refer to the domain ID returned by TypeCreate *************** *** 1094,1099 **** DefineEnum(CreateEnumStmt *stmt) --- 1092,1100 ---- AclResult aclresult; Oid old_type_oid; Oid enumArrayOid; + int num_labels; + + num_labels = list_length(stmt->vals); /* Convert list of names to a name and namespace */ enumNamespace = QualifiedNameGetCreationNamespace(stmt->typeName, *************** *** 1156,1162 **** DefineEnum(CreateEnumStmt *stmt) false); /* Type NOT NULL */ /* Enter the enum's values into pg_enum */ ! EnumValuesCreate(enumTypeOid, stmt->vals, InvalidOid); /* * Create the array type that goes with it. --- 1157,1163 ---- false); /* Type NOT NULL */ /* Enter the enum's values into pg_enum */ ! EnumValuesCreate(enumTypeOid, stmt->vals); /* * Create the array type that goes with it. *************** *** 1197,1202 **** DefineEnum(CreateEnumStmt *stmt) --- 1198,1259 ---- pfree(enumArrayName); } + /* + * AlterEnum + * Registers a new label for an existing enum. + */ + void + AlterEnum (AlterEnumStmt *stmt) + { + Oid enum_type_oid; + TypeName *typename; + HeapTuple tup; + Form_pg_type typTup; + + /* Make a TypeName so we can use standard type lookup machinery */ + typename = makeTypeNameFromNameList(stmt->typeName); + enum_type_oid = typenameTypeId(NULL, typename, NULL); + + tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(enum_type_oid)); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for type %u", enum_type_oid); + + typTup = (Form_pg_type) GETSTRUCT(tup); + + /* Check it's an enum and check user has permission to ALTER the enum */ + checkEnumOwner(tup, typename); + + /* Add the new label */ + AddEnumLabel (enum_type_oid, stmt->newVal, + stmt->newValNeighbour, stmt->newValIsAfter); + + ReleaseSysCache(tup); + } + + + /* + * checkEnumOwner + * + * Check that the type is actually an enum and that the current user + * has permission to do ALTER TYPE on it. Throw an error if not. + */ + static void + checkEnumOwner(HeapTuple tup, TypeName *typename) + { + Form_pg_type typTup = (Form_pg_type) GETSTRUCT(tup); + + /* Check that this is actually a domain */ + if (typTup->typtype != TYPTYPE_ENUM) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not an enum", + TypeNameToString(typename)))); + + /* Permission check: must own type */ + if (!pg_type_ownercheck(HeapTupleGetOid(tup), GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE, + format_type_be(HeapTupleGetOid(tup))); + } /* * Find suitable I/O functions for a type. *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 2901,2906 **** _copyCreateEnumStmt(CreateEnumStmt *from) --- 2901,2919 ---- return newnode; } + static AlterEnumStmt * + _copyAlterEnumStmt(AlterEnumStmt *from) + { + AlterEnumStmt *newnode = makeNode(AlterEnumStmt); + + COPY_NODE_FIELD(typeName); + COPY_STRING_FIELD(newVal); + COPY_STRING_FIELD(newValNeighbour); + COPY_SCALAR_FIELD(newValIsAfter); + + return newnode; + } + static ViewStmt * _copyViewStmt(ViewStmt *from) { *************** *** 4064,4069 **** copyObject(void *from) --- 4077,4085 ---- case T_CreateEnumStmt: retval = _copyCreateEnumStmt(from); break; + case T_AlterEnumStmt: + retval = _copyAlterEnumStmt(from); + break; case T_ViewStmt: retval = _copyViewStmt(from); break; *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 1393,1398 **** _equalCreateEnumStmt(CreateEnumStmt *a, CreateEnumStmt *b) --- 1393,1409 ---- } static bool + _equalAlterEnumStmt(AlterEnumStmt *a, AlterEnumStmt *b) + { + COMPARE_NODE_FIELD(typeName); + COMPARE_STRING_FIELD(newVal); + COMPARE_STRING_FIELD(newValNeighbour); + COMPARE_SCALAR_FIELD(newValIsAfter); + + return true; + } + + static bool _equalViewStmt(ViewStmt *a, ViewStmt *b) { COMPARE_NODE_FIELD(view); *************** *** 2700,2705 **** equal(void *a, void *b) --- 2711,2719 ---- case T_CreateEnumStmt: retval = _equalCreateEnumStmt(a, b); break; + case T_AlterEnumStmt: + retval = _equalAlterEnumStmt(a, b); + break; case T_ViewStmt: retval = _equalViewStmt(a, b); break; *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 182,189 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_ } %type stmt schema_stmt ! AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterFdwStmt ! AlterForeignServerStmt AlterGroupStmt AlterObjectSchemaStmt AlterOwnerStmt AlterSeqStmt AlterTableStmt AlterCompositeTypeStmt AlterUserStmt AlterUserMappingStmt AlterUserSetStmt AlterRoleStmt AlterRoleSetStmt --- 182,189 ---- } %type stmt schema_stmt ! AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterEnumStmt ! AlterFdwStmt AlterForeignServerStmt AlterGroupStmt AlterObjectSchemaStmt AlterOwnerStmt AlterSeqStmt AlterTableStmt AlterCompositeTypeStmt AlterUserStmt AlterUserMappingStmt AlterUserSetStmt AlterRoleStmt AlterRoleSetStmt *************** *** 652,657 **** stmt : --- 652,658 ---- | AlterDatabaseSetStmt | AlterDefaultPrivilegesStmt | AlterDomainStmt + | AlterEnumStmt | AlterFdwStmt | AlterForeignServerStmt | AlterFunctionStmt *************** *** 3862,3867 **** enum_val_list: Sconst --- 3863,3908 ---- { $$ = lappend($1, makeString($3)); } ; + /***************************************************************************** + * + * ALTER TYPE enumtype ADD ... + * + *****************************************************************************/ + + AlterEnumStmt: + ALTER TYPE_P any_name ADD_P Sconst + { + AlterEnumStmt *n = makeNode(AlterEnumStmt); + n->typeName = $3; + n->newVal = $5; + n->newValNeighbour = NULL; + n->newValIsAfter = true; + + $$ = (Node *) n; + } + | ALTER TYPE_P any_name ADD_P Sconst BEFORE Sconst + { + AlterEnumStmt *n = makeNode(AlterEnumStmt); + n->typeName = $3; + n->newVal = $5; + n->newValNeighbour = $7; + n->newValIsAfter = false; + + $$ = (Node *) n; + } + | ALTER TYPE_P any_name ADD_P Sconst AFTER Sconst + { + AlterEnumStmt *n = makeNode(AlterEnumStmt); + n->typeName = $3; + n->newVal = $5; + n->newValNeighbour = $7; + n->newValIsAfter = true; + + $$ = (Node *) n; + } + ; + + /***************************************************************************** * *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 190,195 **** check_xact_readonly(Node *parsetree) --- 190,196 ---- case T_CreateTrigStmt: case T_CompositeTypeStmt: case T_CreateEnumStmt: + case T_AlterEnumStmt: case T_ViewStmt: case T_DropCastStmt: case T_DropStmt: *************** *** 860,865 **** standard_ProcessUtility(Node *parsetree, --- 861,870 ---- DefineEnum((CreateEnumStmt *) parsetree); break; + case T_AlterEnumStmt: /* ALTER TYPE (enum) */ + AlterEnum((AlterEnumStmt *) parsetree); + break; + case T_ViewStmt: /* CREATE VIEW */ DefineView((ViewStmt *) parsetree, queryString); break; *************** *** 1868,1873 **** CreateCommandTag(Node *parsetree) --- 1873,1882 ---- tag = "CREATE TYPE"; break; + case T_AlterEnumStmt: + tag = "ALTER TYPE"; + break; + case T_ViewStmt: tag = "CREATE VIEW"; break; *************** *** 2410,2415 **** GetCommandLogLevel(Node *parsetree) --- 2419,2428 ---- lev = LOGSTMT_DDL; break; + case T_AlterEnumStmt: + lev = LOGSTMT_DDL; + break; + case T_ViewStmt: lev = LOGSTMT_DDL; break; *** a/src/backend/utils/adt/enum.c --- b/src/backend/utils/adt/enum.c *************** *** 14,19 **** --- 14,20 ---- #include "postgres.h" #include "catalog/pg_enum.h" + #include "catalog/pg_type.h" #include "fmgr.h" #include "utils/array.h" #include "utils/builtins.h" *************** *** 22,30 **** #include "libpq/pqformat.h" #include "miscadmin.h" static ArrayType *enum_range_internal(Oid enumtypoid, Oid lower, Oid upper); ! static int enum_elem_cmp(const void *left, const void *right); /* Basic I/O support */ --- 23,52 ---- #include "libpq/pqformat.h" #include "miscadmin.h" + #define BITMAPSIZE 1024 + #define BITMAPBYTES (BITMAPSIZE / 8) + + typedef struct + { + Oid enum_oid; + int32 sort_order; + } enum_sort; + + typedef struct + { + Oid enumtypoid; + Oid bitmap_base; + char bitmap[BITMAPBYTES]; + int label_count; + enum_sort sort_order_list[1]; + } enum_sort_cache; + + static ArrayType *enum_range_internal(Oid enumtypoid, Oid lower, Oid upper); ! static int enum_sort_cmp(const void *left, const void *right); ! static int enum_oid_cmp(const void *left, const void *right); ! static enum_sort_cache *initcache(Oid arg, FunctionCallInfo fcinfo); /* Basic I/O support */ *************** *** 155,167 **** enum_send(PG_FUNCTION_ARGS) /* Comparison functions and related */ Datum enum_lt(PG_FUNCTION_ARGS) { Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(a < b); } Datum --- 177,390 ---- /* Comparison functions and related */ + static enum_sort_cache * + initcache(Oid arg, FunctionCallInfo fcinfo) + { + HeapTuple enum_tup; + Form_pg_enum en; + Oid typeoid; + enum_sort_cache *mycache; + CatCList *list; + int num,i, bm_start, bm_len, start_pos, list_end; + + /* free up anything we've used before) */ + if (fcinfo->flinfo->fn_extra != NULL) + pfree(fcinfo->flinfo->fn_extra); + + /* get the typeoid, label count and the list of values */ + enum_tup = SearchSysCache1(ENUMOID, ObjectIdGetDatum(arg)); + en = (Form_pg_enum) GETSTRUCT(enum_tup); + typeoid = en->enumtypid; + ReleaseSysCache(enum_tup); + list = SearchSysCacheList1(ENUMTYPOIDNAME, ObjectIdGetDatum(typeoid)); + num = list->n_members; + fcinfo->flinfo->fn_extra = + MemoryContextAlloc(fcinfo->flinfo->fn_mcxt, + sizeof(enum_sort_cache) + + (num * sizeof(enum_sort))); + mycache = (enum_sort_cache *) fcinfo->flinfo->fn_extra; + mycache->enumtypoid = typeoid; + mycache->label_count = num; + memset(mycache->bitmap, 0, BITMAPBYTES); + + /* set up the list sorted by Oid */ + for (i = 0; i < num; i++) + { + HeapTuple tup = &(list->members[i]->tuple); + Form_pg_enum list_en = (Form_pg_enum) GETSTRUCT(tup); + + mycache->sort_order_list[i].sort_order = list_en->enumsortorder; + mycache->sort_order_list[i].enum_oid = HeapTupleGetOid(tup); + } + + ReleaseCatCacheList(list); + + qsort(mycache->sort_order_list,mycache->label_count, + sizeof(enum_sort),enum_oid_cmp); + + + /* look for the longest suitable range for the bitmap */ + + bm_len = 0; + bm_start = 0; + + for (start_pos = 0; start_pos < num -1; start_pos ++) + { + for (list_end = start_pos+1; list_end < num; list_end++) + if (mycache->sort_order_list[list_end].sort_order < + mycache->sort_order_list[list_end - 1].sort_order || + mycache->sort_order_list[list_end].enum_oid - + mycache->sort_order_list[start_pos].enum_oid >= BITMAPSIZE) + break; + if (list_end - start_pos > bm_len) + { + bm_len = list_end - start_pos; + bm_start = start_pos; + } + /* + * If the sequence we've just found is bigger than half the list, + * we won't find one bigger + */ + if (bm_len > num / 2) + break; + /* + * If we didn't stop because the oid range was too big, there's no + * point starting to look again earlier than the end of the + * sequence we just found. + */ + if (mycache->sort_order_list[list_end].enum_oid - + mycache->sort_order_list[start_pos].enum_oid >= BITMAPSIZE) + start_pos = list_end - 1; + } + + /* if we found a suitable range, for the bitmap, set it up */ + if (bm_len > 1) + { + int base = mycache->sort_order_list[bm_start].enum_oid; + + mycache->bitmap_base = base; + for (i = 0; i < bm_len; i++) + { + Oid enoid = mycache->sort_order_list[bm_start+i].enum_oid; + int offset = enoid - base; + int bytenum = offset / 8; + int bitmask = 1 << (offset % 8); + mycache->bitmap[bytenum] |= bitmask; + } + } + else + { + /* + * This should be a bit of a pathological case. Normally we'd + * expect at least some small range in sequence. + */ + mycache->bitmap_base = InvalidOid; + } + + return mycache; + } + + /* fast lookup for Oids known to be in order */ + + static inline bool + bitmap_lookup(enum_sort_cache *mycache, Oid arg1, Oid arg2) + { + int offset1 = arg1 - mycache->bitmap_base; + int offset2 = arg2 - mycache->bitmap_base; + int bytenum, bitmask; + if (offset1 < 0 || offset2 < 0 || + offset1 >= BITMAPSIZE || offset2 >= BITMAPSIZE) + return false; + bytenum = offset1 / 8; + bitmask = 1 << (offset1 % 8); + if ((mycache->bitmap[bytenum] & bitmask) == 0) + return false; + bytenum = offset2 / 8; + bitmask = 1 << (offset2 % 8); + if ((mycache->bitmap[bytenum] & bitmask) == 0) + return false; + return true; + } + + /* slower lookup for Oids not known to be in order */ + + static inline int + find_sortorder(enum_sort_cache *mycache, Oid arg) + { + enum_sort *es; + enum_sort srch; + + srch.enum_oid = arg; + es = bsearch(&srch, + mycache->sort_order_list, + mycache->label_count, + sizeof(enum_sort), + enum_oid_cmp); + if (es == NULL) + return -1; + + return es->sort_order; + + } + + /* enum_ccmp is the common engine for all the visible comparison functions */ + + static inline int + enum_ccmp(Oid arg1, Oid arg2, FunctionCallInfo fcinfo) + { + + enum_sort_cache *mycache; + int sort_1, sort_2; + + /* evenly numbered Oids are known to sort right */ + if (arg1 % 2 == 0 && arg2 % 2 == 0) + return arg1 - arg2; + + + /* so are oids that are equal */ + if (arg1 == arg2) + return 0; + + /* get the cached info, set it up if absent */ + mycache = (enum_sort_cache *) fcinfo->flinfo->fn_extra; + if (mycache == NULL ) + mycache = initcache(arg1, fcinfo); + + /* first try the fast lookup, if we can */ + if (OidIsValid(mycache->bitmap_base) && + bitmap_lookup(mycache, arg1, arg2)) + return arg1 - arg2; + + /* try the slow lookup */ + sort_1 = find_sortorder(mycache,arg1); + sort_2 = find_sortorder(mycache,arg2); + + if (sort_1 <= 0 || sort_2 <= 0) + { + /* + * We couldn't find one or both values. + * That means the enum has changed under us, so + * re-initialize the cache and try again. + */ + mycache = initcache(arg1, fcinfo); + sort_1 = find_sortorder(mycache,arg1); + sort_2 = find_sortorder(mycache,arg2); + /* if we fail the second time around, give up */ + if (sort_1 <= 0 || sort_2 <= 0) + elog(ERROR, "values missing for enum %s", + format_type_be(mycache->enumtypoid)); + } + + return sort_1 - sort_2; + } + Datum enum_lt(PG_FUNCTION_ARGS) { Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(enum_ccmp(a,b,fcinfo) < 0); } Datum *************** *** 170,176 **** enum_le(PG_FUNCTION_ARGS) Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(a <= b); } Datum --- 393,399 ---- Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(enum_ccmp(a,b,fcinfo) <= 0); } Datum *************** *** 197,203 **** enum_ge(PG_FUNCTION_ARGS) Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(a >= b); } Datum --- 420,426 ---- Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(enum_ccmp(a,b,fcinfo) >= 0); } Datum *************** *** 206,212 **** enum_gt(PG_FUNCTION_ARGS) Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(a > b); } Datum --- 429,435 ---- Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_BOOL(enum_ccmp(a,b,fcinfo) > 0); } Datum *************** *** 215,221 **** enum_smaller(PG_FUNCTION_ARGS) Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_OID(a <= b ? a : b); } Datum --- 438,444 ---- Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_OID(enum_ccmp(a,b,fcinfo) < 0 ? a : b); } Datum *************** *** 224,230 **** enum_larger(PG_FUNCTION_ARGS) Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_OID(a >= b ? a : b); } Datum --- 447,453 ---- Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! PG_RETURN_OID(enum_ccmp(a,b,fcinfo) > 0 ? a : b); } Datum *************** *** 233,242 **** enum_cmp(PG_FUNCTION_ARGS) Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! if (a > b) ! PG_RETURN_INT32(1); ! else if (a == b) PG_RETURN_INT32(0); else PG_RETURN_INT32(-1); } --- 456,465 ---- Oid a = PG_GETARG_OID(0); Oid b = PG_GETARG_OID(1); ! if (a == b) PG_RETURN_INT32(0); + else if (enum_ccmp(a,b,fcinfo) > 0) + PG_RETURN_INT32(1); else PG_RETURN_INT32(-1); } *************** *** 248,253 **** enum_first(PG_FUNCTION_ARGS) --- 471,477 ---- { Oid enumtypoid; Oid min = InvalidOid; + int min_sort = -1; /* value will never in fact be used */ CatCList *list; int num, i; *************** *** 267,276 **** enum_first(PG_FUNCTION_ARGS) num = list->n_members; for (i = 0; i < num; i++) { ! Oid valoid = HeapTupleHeaderGetOid(list->members[i]->tuple.t_data); ! ! if (!OidIsValid(min) || valoid < min) ! min = valoid; } ReleaseCatCacheList(list); --- 491,504 ---- num = list->n_members; for (i = 0; i < num; i++) { ! HeapTuple tup = &(list->members[i]->tuple); ! Form_pg_enum en = (Form_pg_enum) GETSTRUCT(tup); ! ! if (!OidIsValid(min) || en->enumsortorder < min_sort) ! { ! min = HeapTupleGetOid(tup); ! min_sort = en->enumsortorder; ! } } ReleaseCatCacheList(list); *************** *** 287,292 **** enum_last(PG_FUNCTION_ARGS) --- 515,521 ---- { Oid enumtypoid; Oid max = InvalidOid; + int max_sort = -1; /* value will never in fact be used */ CatCList *list; int num, i; *************** *** 306,315 **** enum_last(PG_FUNCTION_ARGS) num = list->n_members; for (i = 0; i < num; i++) { ! Oid valoid = HeapTupleHeaderGetOid(list->members[i]->tuple.t_data); ! ! if (!OidIsValid(max) || valoid > max) ! max = valoid; } ReleaseCatCacheList(list); --- 535,548 ---- num = list->n_members; for (i = 0; i < num; i++) { ! HeapTuple tup = &(list->members[i]->tuple); ! Form_pg_enum en = (Form_pg_enum) GETSTRUCT(tup); ! ! if (!OidIsValid(max) || en->enumsortorder > max_sort) ! { ! max = HeapTupleGetOid(tup); ! max_sort = en->enumsortorder; ! } } ReleaseCatCacheList(list); *************** *** 382,427 **** enum_range_internal(Oid enumtypoid, Oid lower, Oid upper) i, j; Datum *elems; list = SearchSysCacheList1(ENUMTYPOIDNAME, ObjectIdGetDatum(enumtypoid)); total = list->n_members; elems = (Datum *) palloc(total * sizeof(Datum)); - j = 0; for (i = 0; i < total; i++) { ! Oid val = HeapTupleGetOid(&(list->members[i]->tuple)); - if ((!OidIsValid(lower) || lower <= val) && - (!OidIsValid(upper) || val <= upper)) - elems[j++] = ObjectIdGetDatum(val); } /* shouldn't need the cache anymore */ ReleaseCatCacheList(list); ! /* sort results into OID order */ ! qsort(elems, j, sizeof(Datum), enum_elem_cmp); /* note this hardwires some details about the representation of Oid */ result = construct_array(elems, j, enumtypoid, sizeof(Oid), true, 'i'); pfree(elems); return result; } ! /* qsort comparison function for Datums that are OIDs */ static int ! enum_elem_cmp(const void *left, const void *right) { ! Oid l = DatumGetObjectId(*((const Datum *) left)); ! Oid r = DatumGetObjectId(*((const Datum *) right)); ! if (l < r) ! return -1; ! if (l > r) ! return 1; ! return 0; } --- 615,690 ---- i, j; Datum *elems; + enum_sort *sort_items; + bool left_found; list = SearchSysCacheList1(ENUMTYPOIDNAME, ObjectIdGetDatum(enumtypoid)); total = list->n_members; elems = (Datum *) palloc(total * sizeof(Datum)); + sort_items = (enum_sort *) palloc(total * sizeof(enum_sort)); for (i = 0; i < total; i++) { ! HeapTuple tup = &(list->members[i]->tuple); ! Form_pg_enum en = (Form_pg_enum) GETSTRUCT(tup); ! ! sort_items[i].enum_oid = HeapTupleGetOid(tup); ! sort_items[i].sort_order = en->enumsortorder; } /* shouldn't need the cache anymore */ ReleaseCatCacheList(list); ! /* sort results into sort_order sequence */ ! qsort(sort_items, total, sizeof(enum_sort), enum_sort_cmp); ! ! j = 0; ! left_found = !OidIsValid(lower); ! for (i=0; i < total; i++) ! { ! if (! left_found && lower == sort_items[i].enum_oid) ! left_found = true; ! ! if (left_found) ! elems[j++] = ObjectIdGetDatum(sort_items[i].enum_oid); ! ! if (OidIsValid(upper) && upper == sort_items[i].enum_oid) ! break; ! } /* note this hardwires some details about the representation of Oid */ result = construct_array(elems, j, enumtypoid, sizeof(Oid), true, 'i'); pfree(elems); + pfree(sort_items); return result; } ! /* ! * qsort comparison using sort order, for range routines ! */ static int ! enum_sort_cmp(const void *left, const void *right) { ! enum_sort *l = (enum_sort *) left; ! enum_sort *r = (enum_sort *) right; ! return l->sort_order - r->sort_order; ! } ! ! /* ! * qsort comparison using OID order for comparison search cache ! */ ! static int ! enum_oid_cmp(const void *es1, const void *es2) ! { ! enum_sort *p1, *p2; ! p1 = (enum_sort *)es1; ! p2 = (enum_sort *)es2; ! return p1->enum_oid - p2->enum_oid; } + + *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** *** 6653,6669 **** dumpEnumType(Archive *fout, TypeInfo *tyinfo) PQExpBuffer query = createPQExpBuffer(); PGresult *res; int num, ! i; Oid enum_oid; char *label; /* Set proper schema search path so regproc references list correctly */ selectSourceSchema(tyinfo->dobj.namespace->dobj.name); ! appendPQExpBuffer(query, "SELECT oid, enumlabel " ! "FROM pg_catalog.pg_enum " ! "WHERE enumtypid = '%u'" ! "ORDER BY oid", tyinfo->dobj.catId.oid); res = PQexec(g_conn, query->data); --- 6653,6676 ---- PQExpBuffer query = createPQExpBuffer(); PGresult *res; int num, ! i; Oid enum_oid; char *label; /* Set proper schema search path so regproc references list correctly */ selectSourceSchema(tyinfo->dobj.namespace->dobj.name); ! if (fout->remoteVersion >= 90100) ! appendPQExpBuffer(query, "SELECT oid, enumlabel " ! "FROM pg_catalog.pg_enum " ! "WHERE enumtypid = '%u'" ! "ORDER BY enumsortorder", ! tyinfo->dobj.catId.oid); ! else ! appendPQExpBuffer(query, "SELECT oid, enumlabel " ! "FROM pg_catalog.pg_enum " ! "WHERE enumtypid = '%u'" ! "ORDER BY oid", tyinfo->dobj.catId.oid); res = PQexec(g_conn, query->data); *************** *** 6709,6725 **** dumpEnumType(Archive *fout, TypeInfo *tyinfo) { enum_oid = atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))); label = PQgetvalue(res, i, PQfnumber(res, "enumlabel")); ! if (i == 0) appendPQExpBuffer(q, "\n-- For binary upgrade, must preserve pg_enum oids\n"); appendPQExpBuffer(q, ! "SELECT binary_upgrade.add_pg_enum_label('%u'::pg_catalog.oid, " ! "'%u'::pg_catalog.oid, ", ! enum_oid, tyinfo->dobj.catId.oid); ! appendStringLiteralAH(q, label, fout); ! appendPQExpBuffer(q, ");\n"); } - appendPQExpBuffer(q, "\n"); } ArchiveEntry(fout, tyinfo->dobj.catId, tyinfo->dobj.dumpId, --- 6716,6734 ---- { enum_oid = atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))); label = PQgetvalue(res, i, PQfnumber(res, "enumlabel")); ! if (i == 0) appendPQExpBuffer(q, "\n-- For binary upgrade, must preserve pg_enum oids\n"); appendPQExpBuffer(q, ! "SELECT binary_upgrade.set_next_pg_enum_oid('%u'::pg_catalog.oid);\n", ! enum_oid); ! appendPQExpBuffer(q, "ALTER TYPE %s.", ! fmtId(tyinfo->dobj.namespace->dobj.name)); ! appendPQExpBuffer(q, "%s ADD ", ! fmtId(tyinfo->dobj.name)); ! appendStringLiteralAH(q, label, fout); ! appendPQExpBuffer(q, ";\n\n"); } } ArchiveEntry(fout, tyinfo->dobj.catId, tyinfo->dobj.dumpId, *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** *** 473,489 **** describeTypes(const char *pattern, bool verbose, bool showSystem) gettext_noop("Internal name"), gettext_noop("Size")); if (verbose && pset.sversion >= 80300) appendPQExpBuffer(&buf, " pg_catalog.array_to_string(\n" " ARRAY(\n" " SELECT e.enumlabel\n" " FROM pg_catalog.pg_enum e\n" ! " WHERE e.enumtypid = t.oid\n" ! " ORDER BY e.oid\n" " ),\n" " E'\\n'\n" " ) AS \"%s\",\n", gettext_noop("Elements")); appendPQExpBuffer(&buf, " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n", --- 473,499 ---- gettext_noop("Internal name"), gettext_noop("Size")); if (verbose && pset.sversion >= 80300) + { appendPQExpBuffer(&buf, " pg_catalog.array_to_string(\n" " ARRAY(\n" " SELECT e.enumlabel\n" " FROM pg_catalog.pg_enum e\n" ! " WHERE e.enumtypid = t.oid\n"); ! ! if (pset.sversion >= 90100 ) ! appendPQExpBuffer(&buf, ! " ORDER BY e.enumsortorder\n"); ! else ! appendPQExpBuffer(&buf, ! " ORDER BY e.oid\n"); ! ! appendPQExpBuffer(&buf, " ),\n" " E'\\n'\n" " ) AS \"%s\",\n", gettext_noop("Elements")); + } appendPQExpBuffer(&buf, " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n", *** a/src/include/catalog/indexing.h --- b/src/include/catalog/indexing.h *************** *** 147,152 **** DECLARE_UNIQUE_INDEX(pg_enum_oid_index, 3502, on pg_enum using btree(oid oid_ops --- 147,154 ---- #define EnumOidIndexId 3502 DECLARE_UNIQUE_INDEX(pg_enum_typid_label_index, 3503, on pg_enum using btree(enumtypid oid_ops, enumlabel name_ops)); #define EnumTypIdLabelIndexId 3503 + DECLARE_UNIQUE_INDEX(pg_enum_typid_sortorder_index, 3539, on pg_enum using btree(enumtypid oid_ops, enumsortorder int4_ops)); + #define EnumTypIdSortOrderIndexId 3539 /* This following index is not used for a cache and is not unique */ DECLARE_INDEX(pg_index_indrelid_index, 2678, on pg_index using btree(indrelid oid_ops)); *** a/src/include/catalog/pg_enum.h --- b/src/include/catalog/pg_enum.h *************** *** 35,40 **** CATALOG(pg_enum,3501) --- 35,41 ---- { Oid enumtypid; /* OID of owning enum type */ NameData enumlabel; /* text representation of enum value */ + int4 enumsortorder; /* sort order for this enum label */ } FormData_pg_enum; /* ---------------- *************** *** 48,56 **** typedef FormData_pg_enum *Form_pg_enum; * compiler constants for pg_enum * ---------------- */ ! #define Natts_pg_enum 2 #define Anum_pg_enum_enumtypid 1 #define Anum_pg_enum_enumlabel 2 /* ---------------- * pg_enum has no initial contents --- 49,58 ---- * compiler constants for pg_enum * ---------------- */ ! #define Natts_pg_enum 3 #define Anum_pg_enum_enumtypid 1 #define Anum_pg_enum_enumlabel 2 + #define Anum_pg_enum_enumsortorder 3 /* ---------------- * pg_enum has no initial contents *************** *** 60,67 **** typedef FormData_pg_enum *Form_pg_enum; /* * prototypes for functions in pg_enum.c */ ! extern void EnumValuesCreate(Oid enumTypeOid, List *vals, ! Oid binary_upgrade_next_pg_enum_oid); extern void EnumValuesDelete(Oid enumTypeOid); #endif /* PG_ENUM_H */ --- 62,70 ---- /* * prototypes for functions in pg_enum.c */ ! extern void EnumValuesCreate(Oid enumTypeOid, List *vals); extern void EnumValuesDelete(Oid enumTypeOid); + extern void AddEnumLabel(Oid enumTypeOid, char *newVal, char *neighbour, + bool newValIsAfter); #endif /* PG_ENUM_H */ *** a/src/include/catalog/pg_type_fn.h --- b/src/include/catalog/pg_type_fn.h *************** *** 50,56 **** extern Oid TypeCreate(Oid newTypeOid, char storage, int32 typeMod, int32 typNDims, ! bool typeNotNull); extern void GenerateTypeDependencies(Oid typeNamespace, Oid typeObjectId, --- 50,56 ---- char storage, int32 typeMod, int32 typNDims, ! bool typeNotNull); extern void GenerateTypeDependencies(Oid typeNamespace, Oid typeObjectId, *** a/src/include/commands/typecmds.h --- b/src/include/commands/typecmds.h *************** *** 24,29 **** extern void RemoveTypes(DropStmt *drop); --- 24,30 ---- extern void RemoveTypeById(Oid typeOid); extern void DefineDomain(CreateDomainStmt *stmt); extern void DefineEnum(CreateEnumStmt *stmt); + extern void AlterEnum (AlterEnumStmt *stmt); extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist); extern Oid AssignTypeArrayOid(void); *** a/src/include/nodes/nodes.h --- b/src/include/nodes/nodes.h *************** *** 351,357 **** typedef enum NodeTag T_DropUserMappingStmt, T_AlterTableSpaceOptionsStmt, T_SecLabelStmt, ! /* * TAGS FOR PARSE TREE NODES (parsenodes.h) */ --- 351,357 ---- T_DropUserMappingStmt, T_AlterTableSpaceOptionsStmt, T_SecLabelStmt, ! T_AlterEnumStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) */ *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 2195,2200 **** typedef struct CreateEnumStmt --- 2195,2214 ---- /* ---------------------- + * Alter Type Statement, enum types + * ---------------------- + */ + typedef struct AlterEnumStmt + { + NodeTag type; + List *typeName; /* qualified name (list of Value strings) */ + char *newVal; /* new enum value */ + char *newValNeighbour;/* neighbouring enum value */ + bool newValIsAfter; /* new enum value is after neighbour? */ + } AlterEnumStmt; + + + /* ---------------------- * Create View Statement * ---------------------- */ *** a/src/test/regress/expected/enum.out --- b/src/test/regress/expected/enum.out *************** *** 25,30 **** ERROR: invalid input value for enum rainbow: "mauve" --- 25,92 ---- LINE 1: SELECT 'mauve'::rainbow; ^ -- + -- adding new values + -- + CREATE TYPE planets AS ENUM ( 'venus', 'earth', 'mars' ); + SELECT enumlabel, enumsortorder + FROM pg_enum + WHERE enumtypid = 'planets'::regtype + ORDER by 2; + enumlabel | enumsortorder + -----------+--------------- + venus | 1 + earth | 2 + mars | 3 + (3 rows) + + ALTER TYPE planets ADD 'uranus'; + SELECT enumlabel, enumsortorder + FROM pg_enum + WHERE enumtypid = 'planets'::regtype + ORDER by 2; + enumlabel | enumsortorder + -----------+--------------- + venus | 1 + earth | 2 + mars | 3 + uranus | 4 + (4 rows) + + ALTER TYPE planets ADD 'mercury' BEFORE 'venus'; + ALTER TYPE planets ADD 'saturn' BEFORE 'uranus'; + ALTER TYPE planets ADD 'jupiter' AFTER 'mars'; + ALTER TYPE planets ADD 'neptune' AFTER 'uranus'; + SELECT enumlabel, enumsortorder + FROM pg_enum + WHERE enumtypid = 'planets'::regtype + ORDER by 2; + enumlabel | enumsortorder + -----------+--------------- + mercury | 1 + venus | 2 + earth | 3 + mars | 4 + jupiter | 5 + saturn | 6 + uranus | 7 + neptune | 8 + (8 rows) + + select 'mars'::planets > 'mercury' as using_sortorder; + using_sortorder + ----------------- + t + (1 row) + + -- errors for adding labels + ALTER TYPE planets ADD + 'plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto'; + ERROR: invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto" + DETAIL: Labels must be 63 characters or less. + ALTER TYPE planets ADD 'pluto' AFTER 'zeus'; + ERROR: "zeus" is not an existing label. + DROP TYPE planets; + -- -- Basic table creation, row selection -- CREATE TABLE enumtest (col rainbow); *************** *** 403,409 **** SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow'; SELECT * FROM pg_enum WHERE NOT EXISTS (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid); ! enumtypid | enumlabel ! -----------+----------- (0 rows) --- 465,471 ---- SELECT * FROM pg_enum WHERE NOT EXISTS (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid); ! enumtypid | enumlabel | enumsortorder ! -----------+-----------+--------------- (0 rows) *** a/src/test/regress/sql/enum.sql --- b/src/test/regress/sql/enum.sql *************** *** 16,21 **** SELECT 'red'::rainbow; --- 16,61 ---- SELECT 'mauve'::rainbow; -- + -- adding new values + -- + + CREATE TYPE planets AS ENUM ( 'venus', 'earth', 'mars' ); + + SELECT enumlabel, enumsortorder + FROM pg_enum + WHERE enumtypid = 'planets'::regtype + ORDER by 2; + + ALTER TYPE planets ADD 'uranus'; + + SELECT enumlabel, enumsortorder + FROM pg_enum + WHERE enumtypid = 'planets'::regtype + ORDER by 2; + + ALTER TYPE planets ADD 'mercury' BEFORE 'venus'; + + ALTER TYPE planets ADD 'saturn' BEFORE 'uranus'; + + ALTER TYPE planets ADD 'jupiter' AFTER 'mars'; + + ALTER TYPE planets ADD 'neptune' AFTER 'uranus'; + + SELECT enumlabel, enumsortorder + FROM pg_enum + WHERE enumtypid = 'planets'::regtype + ORDER by 2; + + select 'mars'::planets > 'mercury' as using_sortorder; + + -- errors for adding labels + ALTER TYPE planets ADD + 'plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto'; + + ALTER TYPE planets ADD 'pluto' AFTER 'zeus'; + + DROP TYPE planets; + -- -- Basic table creation, row selection -- CREATE TABLE enumtest (col rainbow);