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