Re: Removing pg_migrator limitations - Mailing list pgsql-hackers
From | Andrew Dunstan |
---|---|
Subject | Re: Removing pg_migrator limitations |
Date | |
Msg-id | 4B2C13DC.6070805@dunslane.net Whole thread Raw |
In response to | Removing pg_migrator limitations (Bruce Momjian <bruce@momjian.us>) |
Responses |
Re: Removing pg_migrator limitations
(Robert Haas <robertmhaas@gmail.com>)
Re: Removing pg_migrator limitations (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
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
pgsql-hackers by date: