Thread: OIDs in pg_type stable across versions?

Hello,

I have written a socket-level interface to PostgreSQL for scsh, an
implementation of the Scheme programming language which provides a
POSIX interface. Unlike Tcl or Perl, Scheme distinguishes between
numbers and strings and booleans and so on (it's a real programming
language :), so I'm looking at how it might be possible to convert
data retrieved from the backend as a character stream into the
appropriate Scheme types.

The fe-be protocol seems to provide sufficient information to do this
type coercion: when the backend sends the attribute data in response
to a SELECT statement, it sends a sequence of tuples (name, type-id,
size). The type-id is an OID from the pg_type table.

Is is reasonable to assume that these oids will be stable across
versions and platforms, or will I have to reconstruct a table from
a `SELECT typname, oid FROM pg_type' each time a connection is
initiated?

(or maybe there's some simpler way to do the type coercions?)

--
Eric Marsden
emarsden @ mail.dotcom.fr
It's elephants all the way down

Moin!

On 20 Jan 1999 11:16:31 +0100
    Eric Marsden <emarsden@mail.dotcom.fr> wrote:
[snipped - low level interface for scheme]
> The fe-be protocol seems to provide sufficient information to do this
> type coercion: when the backend sends the attribute data in response
> to a SELECT statement, it sends a sequence of tuples (name, type-id,
> size). The type-id is an OID from the pg_type table.
>
> Is is reasonable to assume that these oids will be stable across
> versions and platforms, or will I have to reconstruct a table from
> a `SELECT typname, oid FROM pg_type' each time a connection is
> initiated?

I think, yes you should. Think of user defined types! They will be
stored in pg_types also.

What you can do is: cache and share this type information between all
connections initiated from this process.  And then, from time to time,
reload the type information -- or intercept all changes on pg_type
('listen' command) and reload your type infos then.

> (or maybe there's some simpler way to do the type coercions?)

Not in my field of vision.

HAND
  Guido
--
In the beginning there was nothing, and then even that exploded

Guido.Goldstein@t-online.de (Guido Goldstein) writes:
>     Eric Marsden <emarsden@mail.dotcom.fr> wrote:
>> Is is reasonable to assume that these oids will be stable across
>> versions and platforms, or will I have to reconstruct a table from
>> a `SELECT typname, oid FROM pg_type' each time a connection is
>> initiated?

> I think, yes you should. Think of user defined types! They will be
> stored in pg_types also.

Guido's right.  The predefined types (like INT4) have permanently
assigned OIDs, but array types and user-defined types are entered
into the table on-the-fly.  Just reloading the database would likely
change their OIDs, let alone moving to a different version.  Also,
I believe different databases within a single installation have
separate pg_type tables, which are likely to have only the system
types in common.

> What you can do is: cache and share this type information between all
> connections initiated from this process.  And then, from time to time,
> reload the type information -- or intercept all changes on pg_type
> ('listen' command) and reload your type infos then.

I don't think a listen on pg_type would do anything; the system doesn't
issue notifies when changing system tables, AFAIK.  But what you could
do is pull the pg_type table at connection startup, and subsequently
whenever you see a type OID that you haven't got any info about, do
"SELECT ... FROM pg_type WHERE oid = xxx" to add the info to your
table.  Under normal use that wouldn't happen very often, I imagine.

            regards, tom lane