Re: Removing pg_migrator limitations

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I thought of a cleaner approach. CREATE TYPE ENUM will create one enum
> > with the specified oid, and then a server-side function will call
> > EnumValuesCreate() be used to add each additional enum with a specified
> > oid --- no deleting necessary. I will start working on a patch for
> > this.
>
> The approach I originally suggested was to create the enum type with
> *no* members, and then add the values one at a time. It might take a
> tweak to the CREATE TYPE AS ENUM grammar to allow zero members, but
> I don't see any logical problem with such a thing.

Well, I was hesitant to modify the grammar, unless we want the ability
to create enums with zero values. Doing enum with only one value will
not be too complex for me and I don't think binary upgrade should affect
the grammar unless there are other reasons we want to change. I think
it will look like:

-- For binary upgrade, must preserve pg_enum oids
SELECT binary_upgrade.set_next_pg_enum_oid('27258'::pg_catalog.oid);

CREATE TYPE empstatus AS ENUM('hired');

SELECT binary_upgrade.set_next_pg_enum_oid('27259'::pg_catalog.oid);

SELECT binary_upgrade.add_pg_enum_value('42143'::pg_catalog.oid,
'retired');

We do allow tables with no columns, but we allow the addition of columns
to a table, so it makes more sense there.

As far as the ability to add enum values using ALTER TYPE, it seems we
would need a pg_enum.enumnum column like we do for pg_attribute.attnum
and order on that rather than pg_enum.oid. (Binary upgrade would still
need to preserve oids.)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +