Thread: pgsql oid question
I have started experimenting with an access layer for pgsql and have a question. I had someone on this list tell me that the oid values that come back from the server are tag identifiers for that row/column combination and are not type indicators. Yet, when I create multiple tables/columns each having the same type (int32), the same oid keeps being returned. What am I not getting? Can someone point me to a good overview of the internals of PostgreSQL so that I can read up on this? I have scanned through the source but still do not have a clear picture. Thanks Reggie
"Reggie Burnett" <rykr@bellsouth.net> writes:
> I have started experimenting with an access layer for pgsql and have a
> question. I had someone on this list tell me that the oid values that
> come back from the server are tag identifiers for that row/column
> combination and are not type indicators.
I think your someone is confused. There are no identifiers associated
with row/column combinations.
> Yet, when I create multiple
> tables/columns each having the same type (int32), the same oid keeps
> being returned.
The OIDs returned in RowDescription messages uniquely identify datatypes
(more specifically, pg_type rows). They will be stable short of DROP
TYPE/CREATE TYPE shenanigans. (However, user-defined types might not
get the same OIDs assigned after a dump/reload cycle. It's probably
reasonable to treat type OIDs as stable for the life of a connection,
but not as constants of nature.)
OIDs are also used for other purposes, so your confusion may stem from
confusing pg_type OIDs with other OIDs. In the current system
implementation, OIDs are unique row identifiers only within individual
tables --- perhaps not even then, if the table doesn't have a unique
index on its OID field. Thus, a pg_type OID uniquely identifies a
datatype, but that doesn't mean that the same OID number could not
appear in pg_class, pg_rewrite, or other system or user tables.
regards, tom lane
Ok, that adds some clarity. Have base types (int32, etc) had the same oid values for a significant number of versions of PgSQL? What I am getting at is this: can I hard code oid values into an access layer for PgSQL? I see that the Java driver uses select statements back into the db to determine the datatype for a given oid. I know for user-defined types that is required, but for base types is it required? And thanks for the reply! Reggie > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Sunday, January 05, 2003 12:42 AM > To: Reggie Burnett > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] pgsql oid question > > "Reggie Burnett" <rykr@bellsouth.net> writes: > > I have started experimenting with an access layer for pgsql and have a > > question. I had someone on this list tell me that the oid values that > > come back from the server are tag identifiers for that row/column > > combination and are not type indicators. > > I think your someone is confused. There are no identifiers associated > with row/column combinations. > > > Yet, when I create multiple > > tables/columns each having the same type (int32), the same oid keeps > > being returned. > > The OIDs returned in RowDescription messages uniquely identify datatypes > (more specifically, pg_type rows). They will be stable short of DROP > TYPE/CREATE TYPE shenanigans. (However, user-defined types might not > get the same OIDs assigned after a dump/reload cycle. It's probably > reasonable to treat type OIDs as stable for the life of a connection, > but not as constants of nature.) > > OIDs are also used for other purposes, so your confusion may stem from > confusing pg_type OIDs with other OIDs. In the current system > implementation, OIDs are unique row identifiers only within individual > tables --- perhaps not even then, if the table doesn't have a unique > index on its OID field. Thus, a pg_type OID uniquely identifies a > datatype, but that doesn't mean that the same OID number could not > appear in pg_class, pg_rewrite, or other system or user tables. > > regards, tom lane
"Reggie Burnett" <rykr@bellsouth.net> writes:
> Ok, that adds some clarity. Have base types (int32, etc) had the same
> oid values for a significant number of versions of PgSQL? What I am
> getting at is this: can I hard code oid values into an access layer for
> PgSQL?
AFAIK, we have never renumbered an existing standard type. But we
reserve the right to do it. Also, base types have appeared and
disappeared in living memory (eg, datetime).
Minimum prudence would be to #include pg_type.h and then write INT4OID
rather than 23 (for example). A more paranoid approach is to establish
a typename<->oid cache on the client side and not believe anything you
haven't probed during the current connection. The Java driver takes the
paranoid approach, but some other clients such as the ODBC driver rely
on #defines. The tradeoffs are pretty obvious, so make your own choice
about what to do.
regards, tom lane