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.
>
> 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. We do something similar for toast
> tables now, but it is easier there because the oids are actually file
> system files.
>
> There is no ability to specify an OID column value on insert. However,
> pg_migrator has the ability to call backend C functions via shared
> library functions so it could potentially insert the needed system
> catalog dummy rows. As far as creating rows with the proper oids, we
> could modify the SQL grammar to allow it, or modify DefineType() so it
> accepts oids and passes them to TypeCreate(), or a simpler approach
> would be to set the oid counter before calling CREATE TYPE, but that
> would be error-prone because other oids might be assigned in
> indeterminate order --- we removed that code from pg_migrator for toast
> tables before 8.4 shipped, so I am not excited to re-add it. The same
> approach is necessary for enums.
>
> Another approach could be to create the dummy rows, load all of the
> pg_dump schema, then renumber the rows to the proper oids, but this
> assumes that I will be able to find all references to the current oids
> and renumber those too.
>
> Seems I need some help here.
>
>

I thought there was a suggestion that we would be able to specify the
oids in the SQL that creates the types, along the lines of:

create type foo as enum ( ...) with oids ( ... );

Is that a non-starter? I imagine it would need to require some special
setting to be enabled to allow it.

cheers

andrew