Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.1/8.9.1) with ESMTP id KAA22967 for ; Wed, 20 Jan 1999 10:57:36 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA06604 for ; Wed, 20 Jan 1999 10:55:28 -0500 (EST) To: PG-Interfaces Subject: Re: [INTERFACES] OIDs in pg_type stable across versions? In-reply-to: Your message of 20 Jan 1999 14:42:36 +0100 Date: Wed, 20 Jan 1999 10:55:28 -0500 Message-ID: <6602.916847728@sss.pgh.pa.us> From: Tom Lane Guido.Goldstein@t-online.de (Guido Goldstein) writes: > Eric Marsden 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