Re: Removing pg_migrator limitations - Mailing list pgsql-hackers

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > There are several pg_migrator limitations that appeared late in the 8.4
> > development cycle and were impossible to fix at that point.  I would
> > like to fix them for Postgres 8.5:
> > 
> >         o  a user-defined composite data type
> >         o  a user-defined array data type
> >         o  a user-defined enum data type
> > 
> > I have discussed this with Alvaro.  I think pg_migrator needs the
> > ability to set the pg_type.oid and pg_enum.oid for user-defined
> > composites, arrays, and enums to match the values in the old server, and
> > hence match references to those rows in user data tables.
> 
> To be more precise, the pg_enum.oid needs to be set for ENUM types;
> there's no need for setting the pg_type.oid (for ENUM types).  I don't
> know about composites but I think the problem with user defined arrays
> is the OID of the element type, not the array itself.

Yes, good point.  I can see where the oids are assigned in our C code:
       oids[i] = GetNewOid(pg_enum);
array_oid = GetNewOid(pg_type);

I need a way of controlling that.  Now, ideally, I would just be able to
add an optional oid field to DefineType() and call it from a server-side
C function called by pg_migrator, but the problem is that that function
assumes it is receiving a complex struct DefineStmt which can't easily
be created by pg_migrator.

> > The general solution will involve creating place-hold rows in pg_type
> > and pg_enum with the desired oids, and deleting those placeholder rows
> > at the time pg_dump creates the new type or enum, and passing the
> > desired oid to the creation command.
> 
> I don't think there's a need for pg_enum placeholders.  Just create them
> with the correct OIDs as the first step.  Nobody else is going to use
> pg_enum.oids anyway.  Again, I don't know about arrays or composites.

That will make things easier because of the large number of oids
consumed by enumerated types.

I am now thinking that setting the oid counter before calling CREATE
TYPE/ENUM might be the cleanest, and of course with pg_dump setting this
all up when in --binary-upgrade mode.  It does make pg_migrator
dependent on the order of oid allocation in those routines.  It also
might make some migrations impossible if concurrent enum creation caused
gaps in the assignment of oids in a single enumerated type.

A crazier idea would be for pg_migrator to set server-side global
variables that contain the oids to be used.  pg_dump would call those
functions to set and clear the global variables when in --binary-upgrade
mode, and the backend code would consult those variables before calling
GetNewOid(), or GetNewOid() would consult those global variables.

You can now see why this was not fixed in 8.4.  :-(

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +