Re: Removing pg_migrator limitations

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.

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

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support