Re: After upgrading libpq, the same function(PQftype) call returns a different OID

Hello,

I think the column type identification with PQftype() needs some review/clarification.

It's a pity that libpq does not have an API to return directly the actual type name of a column.

The doc<https://www.postgresql.org/docs/17/libpq-exec.html#LIBPQ-PQFTYPE> says:

You can query the system table pg_type to obtain the names and properties of the various data types. The OIDs of the built-in data types are defined in the file catalog/pg_type_d.h in the PostgreSQL installation's include directory.

After building PostgreSQL 17.4 from the sources, I cannot find this pg_type_d.h header file in the installation directory. Maybe I am missing some configure option?

Anyway, I found the file in the sources:

src/include/catalog/pg_type_d.h

And I can read this comment:

/*
* Backwards compatibility for ancient random spellings of pg_type OID macros.
* Don't use these names in new code.
*/
#define CASHOID MONEYOID
#define LSNOID PG_LSNOID

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
...

I am expecting something like:

#define PG_TYPE_BOOL 16
#define PG_TYPE_BYTEA 17
#define PG_TYPE_CHAR 18
#define PG_TYPE_NAME 19
#define PG_TYPE_INT8 20
#define PG_TYPE_INT2 21
#define PG_TYPE_INT2VECTOR 22
...

Seb

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Saturday, March 15, 2025 8:02 PM
To: M Tarkeshwar Rao <m(dot)tarkeshwar(dot)rao(at)ericsson(dot)com>
Cc: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

M Tarkeshwar Rao <m(dot)tarkeshwar(dot)rao(at)ericsson(dot)com> writes:
> We are using PostgreSQL libpq in our application. The code worked fine for the past four years, but after upgrading the library, the function PQftype is returning unexpected values for some columns.
> Specifically, the issue occurs with a column of type timestamp(3) without time zone.

The OID of type timestamp has not changed. Perhaps you are now
querying some other table. I'd suggest looking into pg_type to
find out what type is represented by the OID you're now getting,
and then searching pg_attribute to see what's using that.

select typname from pg_type where oid = 123456;

select attrelid::regclass, attname from pg_attribute where atttypid = 123456;

Also, if you really do mean that you changed only libpq and
nothing about the server side, I'd have to guess that you're
connecting to some other database than before. That would be
surprising, but with zero details it's hard to debug.

regards, tom lane