Re: Removing pg_migrator limitations - Mailing list pgsql-hackers

> Bruce Momjian wrote:
>> Seems I need some help here.

I'm willing to work on this --- it doesn't look particularly fun but
we really need it.

Andrew Dunstan <andrew@dunslane.net> writes:
> 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.

The more I think about it the less I want such warts placed in the
regular SQL syntax for creation commands.  As soon as we add a wart like
that we'll be stuck with supporting it forever.  Whatever we do here
should be off in a little corner that only pg_migrator can get at.

And we already have a way to manage that: there's already something
in pg_migrator to let it install special functions that are present
only while migrating.  So I suggest that we make whatever hacks are
needed available only at the C-code level, and let pg_migrator get
at them via its special functions.

In practice, this would mean teaching pg_dump to call these functions
when it is making a --binary_upgrade dump.  The reason I think this
is less of a support hazard than changing SQL statements is that there
is no promise or intention that a --binary_upgrade dump will load into
anything but the specific PG version that it's intended for.  (We
could, and probably should, add some version labeling to the dump to
help enforce that.)

At the moment it appears that we need the following hacks:

* ability to control the OIDs assigned to user tables and types.
Because a table also has a rowtype, this means at least two separate
state variables.  And we already knew we had to control the OIDs
assigned to toast tables.  I'm imagining dump output like
select pg_migrator_set_next_table_oid(123456);select pg_migrator_set_next_type_oid(12347);select
pg_migrator_set_next_toast_table_oid(123458);
CREATE TABLE ...

where the functions cause static variables to become set, and the
core code gets changed to look like
if (next_table_oid){    newoid = next_table_oid;    next_table_oid = 0;}else    newoid = GetNewOid(...);

in selected places where currently there's just a GetNewOid(...) call.

* ability to control the OIDs assigned to enum values.  To keep this
sane I think the easiest way is to have pg_migrator have a function
that adds one value with a predetermined OID to an existing enum.
So instead of CREATE TYPE foo AS ENUM ('bar', 'baz', ...)
I envision the --binary_upgrade dump output looking like
-- force the OID of the enum type itselfselect pg_migrator_set_next_type_oid(12347);
CREATE TYPE foo AS ENUM ();
select pg_migrator_add_enum_value(12347, 'bar', 12348);select pg_migrator_add_enum_value(12347, 'baz', 12349);...


I don't see any value in the placeholder-row approach Bruce suggests;
AFAICS it would require significantly uglier backend hacks than the
above because dealing with an already-present row would be a bigger
code change.

Comments?
        regards, tom lane