Question on datatypes returned for "select oid, typname from pg_type"
Hi all,
for an API I want to make some changes to, I need to know the datatype being used for a record's column. In fact the entire record is to be transferred into a dictionary. From my web research I came to use
select oid, typname from pg_type
to find out what datatypes exist. When checking on a certain field, it returned 17 as a type, thus being a bytea. That's actually the question now, because, is it always that bytea gets oid 17 or are these assignments of type name and oid dependent on the database installation, version of pg or whatever else I might be missing? I would prefer to hardcode how these numbers are interpreted for performance reasons, so even if they do depend on other criteria, my next question would be how often throughout pg-history did they change, if they do not depend on the individual installation.
Thank you in advance for clarifying.
Alex
Alexander Reichstadt <lxr@mac.com> writes:
to find out what datatypes exist. When checking on a certain field, it
returned 17 as a type, thus being a bytea. That's actually the
question now, because, is it always that bytea gets oid 17 or are
these assignments of type name and oid dependent on the database
installation, version of pg or whatever else I might be missing?
The built-in data types have hand-assigned OIDs, as depicted in
src/include/catalog/pg_type.h. While those aren't quite
guaranteed-frozen, we've never changed one that I can recall, and are
not very likely to in the future. However, any type that's not built-in
has an OID that will vary across databases. As a rule of thumb,
you could assume that any OID under 10000 is hand-assigned and won't
change.
regards, tom lane
On 10/03/12 09:15, Tom Lane wrote:
Alexander Reichstadt<lxr@mac.com> writes:
to find out what datatypes exist. When checking on a certain field, it
returned 17 as a type, thus being a bytea. That's actually the
question now, because, is it always that bytea gets oid 17 or are
these assignments of type name and oid dependent on the database
installation, version of pg or whatever else I might be missing?The built-in data types have hand-assigned OIDs, as depicted in
src/include/catalog/pg_type.h. While those aren't quite
guaranteed-frozen, we've never changed one that I can recall, and are
not very likely to in the future. However, any type that's not built-in
has an OID that will vary across databases. As a rule of thumb,
you could assume that any OID under 10000 is hand-assigned and won't
change.regards, tom lane
Hmm...
That kind of reasoning makes me nervous!
What if some developer wants to change the OIDs assigned for some
reason, and is blissfully unaware that people depend on those precise
values?
I once did a minor cosmetic change to a report program on a mainframe. A
few weeks later a user area complained their SAS program was generating
errors. Turns out they were processing the print file & my added spaces
caused them grief. Admittedly, it could be argued that people should not
be feeding print files into programs like that.
Essentially, if people are relying on specific values, I think those
values should be documented appropriately, and the associated program
code should reflect possible usage elsewhere. How best to achieve that
in this example, I don't know. Possibly a simple comment where the
numbers are assigned plus documentation in the manual (I've a vague idea
this might already have been done).
I guess the main trigger for me is the idea that they have not changed
for a long time and unlikely to be, and similar reasoning.
Cheers,
Gavin
P.S. Hopes no one looks at the cruddy code I'm writing...
or maybe they should,
and then I could correct silly assumptions,
before they bite me further down the track... :-)
Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
On 10/03/12 09:15, Tom Lane wrote:
The built-in data types have hand-assigned OIDs, as depicted in
src/include/catalog/pg_type.h. While those aren't quite
guaranteed-frozen, we've never changed one that I can recall, and are
not very likely to in the future.
That kind of reasoning makes me nervous!
What if some developer wants to change the OIDs assigned for some
reason, and is blissfully unaware that people depend on those precise
values?
The committers are well aware of this, at least, so I would be very
surprised to see a random reassignment of type OIDs slip through.
regards, tom lane