Re: Re: [Pg-migrator-general] Composite types break pg_migrated tables

On Thu, Aug 6, 2009 at 4:32 AM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>> preventing a clash might be fairly difficult.
>>
>> Yeah, I was just thinking about that.  The easiest way to avoid
>> collisions would be to make pg_dump (in --binary-upgrade mode)
>> responsible for being sure that *every* new pg_type and pg_class row
>> OID matches what it was in the old DB.
>
> As we already have WITH OIDS for CREATE TABLE command, maybe adding
> support for WITH OID ... to the necessary commands would do the trick?
>
> Instead of messing with pg_type, pg_dump would then have to issue a OID
> 'decorated' command such as
>  CREATE TYPE footype ... WITH OID 27604;

Unfortunately it's not enough to just do this with 'create type' and
'create type as', we also have to do this with 'create table'. Some
people (like me) use tables as composite types because of the extra
flexibility it gives you. So, potentially, OIDs for enums, tables,
and types needs to be preserved.

I am very much in support for any system that allows creation of a
type with a specific OID. This is not just a problem with the
migrator, but will allow for more robust transfers of data over the
binary protocol (think binary dblink) without resorting to hacks to
that do lookups based on typename.

IOW, this setting specific OIDs should ideally be exposed at the SQL
level and should be able to be done for any type that can be part of a
container.

merlin