Re: Removing pg_migrator limitations

Tom Lane wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > On Wed, Dec 23, 2009 at 7:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> The reason that isn't implemented is that it's *hard* --- in fact,
> >> it appears to be entirely impossible in the general case, unless you're
> >> willing to change existing values of the enum on-disk.
>
> > Shouldn't adding new ones be easy?
>
> No, not if you care about where they end up in the type's sort ordering.
>
> In pg_migrator's case that's not an issue because it's going to force
> the OID numbering for each of the elements. However, an ADD ENUM VALUE
> option that *doesn't* use a predetermined OID is going to end up
> inserting the new value at a not-very-predictable place. I do not think
> we should expose a half-baked behavior like that as standard SQL syntax.
> If we're going to implement something whose ambitions only extend to
> satisfying pg_migrator's needs, then it should be a specialized
> pg_migrator function.

I looked at DefineEnum() and basically adding the ability to add enums
would put the new enum after the existing ones unless the OID counter
has wrapped around and is less than the oid counter at the time the enum
type was created, in which case it will be listed as before the existing
values. I wasn't aware enum ordering is something we tried to maintain.
One issue is that we are not supporting the addition of enum values even
for people who don't care about the ordering of enums (which I bet might
be the majority.)

I can think of a few approaches for pg_migrator:

1) Create an oid array in a permanent memory context and have
DefineEnum() read from that.
2) Renumber the enum entries after they are created but before
any of their oids are stored in user tables.

Both can be done by pg_dump with proper server-side functions. The
problem with #2 are cases where the old and new oid ranges overlap,
e.g.:

1 2 3

becomes:

2 3 4

In that case, you can't just renumber because of oid collisions that
would invalidate the oid index on pg_enum. Even the ordering of
renumbering might not be consistent, e.g.:

old 1 2 3 12 13 14

new 2 3 4 11 12 13

Starting renumbering from the front or back would both fail.

--
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. +