Обсуждение: change oid of a pg_type

Is there any way to adjust the oid of an already created pg_type (plus
dependent tables like pg_attribute)?  C code inside the backend is
ok...I have two databases and need to keep oid in sync.

(or, create a type at a particular oid slot would be ok too).  note,
this is not at bootstrap time.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> Is there any way to adjust the oid of an already created pg_type

No.
        regards, tom lane

Merlin Moncure escribió:

> (or, create a type at a particular oid slot would be ok too).  note,
> this is not at bootstrap time.

We needed fixed OIDs for some things in Replicator, and the only way I
could see about it was to hack up a special bootstrap mode.

It's a pain :-(  But since we patch the backend code already, it wasn't
entirely out of the question ...

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> Is there any way to adjust the oid of an already created pg_type
> 
> No.
> 
> 

Then why are oids sent in some type's external binary format?  If you 
can't fix oids, external format is useless to the outside world.  Since 
you CAN modify a type's name all day long, shouldn't schema.typname be 
the external way to reference types?  Or, perhaps, another mechanism. 
There are some very powerful things that could be achieved, with a more 
portable way of referencing types.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Chernow <ac@esilo.com> writes:
> Tom Lane wrote:
>> Merlin Moncure <mmoncure@gmail.com> writes:
>>> Is there any way to adjust the oid of an already created pg_type
>> 
>> No.

> Then why are oids sent in some type's external binary format?

How is that related?

> If you 
> can't fix oids, external format is useless to the outside world.

You have to be prepared to find out what type the OID belongs to.
There's at least one client library (maybe JDBC) that maintains
a cache of a SELECT from pg_type for exactly this purpose.  Most
others don't care because they don't really have any special
support for non-builtin types --- but if you do, you have to deal
with the fact that their OIDs aren't fixed.
        regards, tom lane

Tom Lane wrote:
> Andrew Chernow <ac@esilo.com> writes:
>> Tom Lane wrote:
>>> Merlin Moncure <mmoncure@gmail.com> writes:
>>>> Is there any way to adjust the oid of an already created pg_type
>>> No.
> 
>> Then why are oids sent in some type's external binary format?
> 
> How is that related?
> 

Well, the format is called "external", yet the oid is internal.  It seem 
like a contradiction.  Maybe I'm being to literal but i think external 
format should present an identifier other servers can understand/map.

>> If you 
>> can't fix oids, external format is useless to the outside world.
> 
> You have to be prepared to find out what type the OID belongs to.
> There's at least one client library (maybe JDBC) that maintains
> a cache of a SELECT from pg_type for exactly this purpose.  Most
> 

Yeah, libpqtypes looks them up by schema.name as one registers them, 
PQregisterTypeHandler.  Although this works, it feel more like a work 
around.  I think it would be cleaner if a dns-like resolution was not 
required.

Andrew