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

From: Sebastien Flaesch <[email-masked]>
Date: Sun, 16 Mar 2025 09:30:02 +0000

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 <[email-masked]>
Sent: Saturday, March 15, 2025 8:02 PM
To: M Tarkeshwar Rao <[email-masked]>
Cc: [email-masked] <[email-masked]>
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 <[email-masked]> 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

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 18 Mar 2025 11:41:39 -0700

On 3/16/25 02:30, Sebastien Flaesch wrote:
> 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?

Went I built from source in ended up in:

/usr/local/pgsql/include/server/catalog/

-- 
Adrian Klaver
[email-masked]

---

From: Sebastien Flaesch <[email-masked]>
Date: Wed, 19 Mar 2025 06:41:25 +0000

You are right Adrian, I did not search properly I found the header file here:

sf@toro:/opt3/dbs/pgs/17.4$ ls -l include/postgresql/server/catalog/pg_type_d.h
-rw-r--r-- 1 sf sf 9672 Mar 13 17:05 include/postgresql/server/catalog/pg_type_d.h

I was not expecting this file to be in a "server" folder, when it's to be used for client apps.

And still, I do not trust the content.

Seb

________________________________
From: Adrian Klaver <[email-masked]>
Sent: Tuesday, March 18, 2025 7:41 PM
To: Sebastien Flaesch <[email-masked]>; Tom Lane <[email-masked]>; M Tarkeshwar Rao <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID

On 3/16/25 02:30, Sebastien Flaesch wrote:
> <https://urldefense.com/v3/__https://www.postgresql.org/docs/17/libpq-exec.html*LIBPQ-PQFTYPE__;Iw!!I_DbfM1H!BrhvYoUAjZeszdg-ZMusy9M6WcTcHnTVpF22U_PzygM-UxQsQ0oa34TMFyp3Asr6-8L3nJQDSkUR9533wp_t-V3RsEXlNg$ > says:
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 19 Mar 2025 09:53:31 -0700

On 3/18/25 23:41, Sebastien Flaesch wrote:
> sf@toro:/opt3/dbs/pgs/17.4$ ls -l 
> include/postgresql/server/catalog/pg_type_d.h
> -rw-r--r-- 1 sf sf 9672 Mar 13 17:05 
> include/postgresql/server/catalog/pg_type_d.h
> 

Not surprising. As I understand it this is the code used to build the 
type entries in the system catalog pg_type. As was mentioned in your 
previous link:

https://www.postgresql.org/docs/17/libpq-exec.html#LIBPQ-PQFTYPE

the suggested way to get type information is:

"You can query the system table pg_type to obtain the names and 
properties of the various data types. "

> And still, I do not trust the content.

Then do as suggested above.

---

From: Tom Lane <[email-masked]>
Date: Wed, 19 Mar 2025 13:22:47 -0400

> type entries in the system catalog pg_type.

More the other way around: pg_type_d.h is built from the authoritative
source files pg_type.h and pg_type.dat, according to the process
described here:

https://www.postgresql.org/docs/devel/bki.html

>> And still, I do not trust the content.

Why not?  If it's the "Backwards compatibility" comment that's
bothering you, a look at pg_type.h will show you that that's
only intended to apply to the CASHOID and LSNOID symbols.
Everything below that in pg_type_d.h is machine-generated.

			regards, tom lane

---

From: Sebastien Flaesch <[email-masked]>
Date: Thu, 20 Mar 2025 07:17:47 +0000

Let's not deviate from my request:

I have implemented various DB client modules using the C client APIs, for Oracle DB, SQL Server, IBM DB2, MySQL/MariaDB, SQLite and PostgreSQL.

While I like PostgreSQL a lot, this is the only case where I have to define myself the column type ids, to implement a "describe column" feature.

ODBC has SQLDescribeCol() / SQLDescribeColW() and predefined SQL_* constants like SQL_INTEGER, SQL_VARCHAR ...

Native PostgreSQL built-in SQL types should be listed in a .h header of the C client API

I do not want to execute SQL to identify a column type returned by PQftype().
This is not efficient, even if I would cache the mapping of the type oid to a type name.

I just want to do this:

static int prepareField(SqlStatement *st, int i)
{
    int pgftype = PQftype(st->pgResult, i);
    int pgfmod = PQfmod(st->pgResult, i);
    ...
    switch (pgftype) {
    case PG_TYPE_BOOL:
        ...
        break;
    case PG_TYPE_CHAR:
    case PG_TYPE_BPCHAR:
    case PG_TYPE_VARCHAR:
        ...
        break;

And today I have to define all these type ids:

/* ! Should be provided by a PostgreSQL header file! */
#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
#define PG_TYPE_INT4                      23
#define PG_TYPE_REGPROC                   24
#define PG_TYPE_TEXT                      25
#define PG_TYPE_OID                       26
#define PG_TYPE_TID                       27
#define PG_TYPE_XID                       28
#define PG_TYPE_CID                       29
...

I don't care if this list is generated when building PostgreSQL from sources.

I expect however that the type oids for built-in types remain the same forever.

Seb
________________________________
From: Tom Lane <[email-masked]>
Sent: Wednesday, March 19, 2025 6:22 PM
To: Adrian Klaver <[email-masked]>
Cc: Sebastien Flaesch <[email-masked]>; M Tarkeshwar Rao <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID

https://urldefense.com/v3/__https://www.postgresql.org/docs/devel/bki.html__;!!I_DbfM1H!GM5pJKRPNVArTRiyYGhyIZrVAgLo7RZl1FSS5kG4IZvWLW75bP4zu1P7yVuLucHd3_FbuKym1-W3Wv0iEs6X$

                        regards, tom lane

---

From: Luca Ferrari <[email-masked]>
Date: Thu, 20 Mar 2025 11:47:37 +0100

> I have implemented various DB client modules using the C client APIs, for Oracle DB, SQL Server, IBM DB2, MySQL/MariaDB, SQLite and PostgreSQL.

Good for you!
I don't think anybody is trying to deviate from your request, the
point is as described in the documentation of PQftype you have been
already pointed out. And according to me, the approach is flexible
enough to allow for extending the type system without problems.
Last, it is not clear to me what the original problem (getting a
different oid from your libpq example) was generated from: did you
queried the catalog?

Luca

---

From: Tom Lane <[email-masked]>
Date: Thu, 20 Mar 2025 10:31:39 -0400

> Native PostgreSQL built-in SQL types should be listed in a .h header of the C client API

They are.  You were already pointed to it. The fact that you don't
like how that file's name is spelled is not really going to
impress anyone.

			regards, tom lane

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 20 Mar 2025 08:33:07 -0700

On 3/20/25 00:17, Sebastien Flaesch wrote:
> I have implemented various DB client modules using the C client APIs, 
> for Oracle DB, SQL Server, IBM DB2, MySQL/MariaDB, SQLite and PostgreSQL.
> 
> While I like PostgreSQL a lot, this is the only case where I have to 
> define myself the column type ids, to implement a "describe column" feature.
> 
> ODBC has SQLDescribeCol() / SQLDescribeColW() and predefined SQL_* 
> constants like SQL_INTEGER, SQL_VARCHAR ...
> 

You could borrow from here:

https://github.com/postgresql-interfaces/psqlodbc/blob/main/pgtypes.h

---

From: Sebastien Flaesch <[email-masked]>
Date: Thu, 20 Mar 2025 15:42:39 +0000

Tom,
They are.  You were already pointed to it. The fact that you don't
like how that file's name is spelled is not really going to
impress anyone.
I don't care about the .h file name or location, what scares me is this:

/*

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
#define INT2VECTOROID 22
#define INT4OID 23
#define REGPROCOID 24

If I am missing something, then please point me to the correct .h file that contains #define constants without this scary comment.

OR.... ( I guess I start to understand the code... ) it this comment only for:

#define CASHOID MONEYOID
#define LSNOID  PG_LSNOID

???

And sorry if I consider constant names like these (without any prefix such as PG_TYPE_)

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

... are looking more like names not to be used!

Arrogance does not help here, clarity and better API doc would.

Seb
________________________________
From: Tom Lane <[email-masked]>
Sent: Thursday, March 20, 2025 3:31 PM
To: Sebastien Flaesch <[email-masked]>
Cc: Adrian Klaver <[email-masked]>; M Tarkeshwar Rao <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID

                        regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 20 Mar 2025 16:49:37 +0100

> OR.... ( I guess I start to understand the code... ) it this comment only for:

That's what Tom already replied, yes. --DD

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 20 Mar 2025 09:25:38 -0700

On Thu, Mar 20, 2025 at 8:42 AM Sebastien Flaesch <[email-masked]>
wrote:

>
> */**
> * * 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
> #define INT2VECTOROID 22
> #define INT4OID 23
> #define REGPROCOID 24
>
> If I am missing something, then please point me to the correct .h file
> that contains #define constants without this scary comment.
>

Yes, that blank line separating LSNOID and BOOLOID blocks the comment from
applying to the items after the blank line.  That is a fairly common
convention, using whitespace to break things up.  Also, assigning one macro
to another is quite distinct from assigning a constant to a name; making
the "backward compatibility" aspect of this comment only meaningfully apply
to those two items.

> And sorry if I consider constant names like these (without any prefix such

We spelled PG_TYPE_  as OID and put it on the end.  A boolean as an object
is by definition a type.  Context clues are important, not every pattern
gets spelled out in prose.

> Arrogance does not help here, clarity and better API doc would.
To my knowledge the current API doc for this hasn't had any comments of
this sort for a very long time.  All documentation can be improved because
every reader comes at it from a different starting point.  Do you have a
concrete suggestion for what you think should be changed, and why?  My take
away from this thread is that a single report isn't usually enough to go
searching hard for ways to tweak the documentation for readability; nor has
this one pointed out any outright errors to be fixed.  In short, we expect
that some subset of readers will ask us questions on the mailing list
because that is the reality of things.

That said, I am curious as to the education flow a developer, not linking
in this specific header to their code, would go through in order to learn
about type OIDs and make use of them in their project.  Maybe that flow is
good, maybe not.  It's a rare flow and there are many things to do in the
project.  So my curiosity may not get satiated.  As you brought this up and
are invested in the outcome you have more motivation than probably anyone
else to dive into it and make concrete suggestions for change.

All that said, a comment at the top of what is probably the most important
section of the header seems warranted.  Even if it is just mostly
formality.  Mentioning the constant-ness of the integers should be part of
that.

David J.

---

From: Sebastien Flaesch <[email-masked]>
Date: Thu, 20 Mar 2025 16:40:39 +0000

Dominique,
That's what Tom already replied, yes. --DD
My bad! I missed that answer from Tom.

Thanks.
Seb

________________________________
From: Dominique Devienne <[email-masked]>
Sent: Thursday, March 20, 2025 4:49 PM
To: Sebastien Flaesch <[email-masked]>
Cc: Tom Lane <[email-masked]>; Adrian Klaver <[email-masked]>; M Tarkeshwar Rao <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID

---

From: Sebastien Flaesch <[email-masked]>
Date: Thu, 20 Mar 2025 17:24:01 +0000

David,
That said, I am curious as to the education flow a developer, not linking in this specific header to their code, would go through in order to learn about type OIDs and make use of them in their project.  Maybe that flow is good, maybe not.  It's a rare flow and there are many things to do in the project.  So my curiosity may not get satiated.  As you brought this up and are invested in the outcome you have more motivation than probably anyone else to dive into it and make concrete suggestions for change.

Any project using the PostgreSQL C API to implement an interface/module for another programming language will need to implement basic SQL handling functions to prepare, execute, fetch rows, AND introspect SQL statements parts like column information (name, type).

We have for ex a SQL handler class in Genero BDL, providing following methods:

  DEFINE h base.SqlHandle
  LET h = base.SqlHandle.create()
  CALL h.prepare("SELECT ....")
  CALL h.open()
  DISPLAY h.getResultType(1) : type name of column #1
  DISPLAY h.getResultType(2) : type name of column #2
  ...

About the PQftype() doc, the header file catalog/pg_type_d.h is mentioned, but I think it is missing the fact that type OID constants can be recognized in that header file, with the "OID" suffix.

While I understand that it's additional work on doc maintenance, I would in fact expect an exhaustive list of built-in data type ids just like in:

https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver16

https://dev.mysql.com/doc/c-api/8.4/en/c-api-prepared-statement-type-codes.html

https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=constants-sql-data-type#ids_esqlc_0123__sii-03-97666

But I can understand that this is maybe not natural for PostgreSQL implementors, because of the flexible data type system and the fact that even built-in type ids are generated.

Seb

________________________________
From: David G. Johnston <[email-masked]>
Sent: Thursday, March 20, 2025 5:25 PM
To: Sebastien Flaesch <[email-masked]>
Cc: Tom Lane <[email-masked]>; Adrian Klaver <[email-masked]>; M Tarkeshwar Rao <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID

On Thu, Mar 20, 2025 at 8:42 AM Sebastien Flaesch <[email-masked]<mailto:[email-masked]>> wrote:

/*

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
#define INT2VECTOROID 22
#define INT4OID 23
#define REGPROCOID 24

Yes, that blank line separating LSNOID and BOOLOID blocks the comment from applying to the items after the blank line.  That is a fairly common convention, using whitespace to break things up.  Also, assigning one macro to another is quite distinct from assigning a constant to a name; making the "backward compatibility" aspect of this comment only meaningfully apply to those two items.

We spelled PG_TYPE_  as OID and put it on the end.  A boolean as an object is by definition a type.  Context clues are important, not every pattern gets spelled out in prose.

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

To my knowledge the current API doc for this hasn't had any comments of this sort for a very long time.  All documentation can be improved because every reader comes at it from a different starting point.  Do you have a concrete suggestion for what you think should be changed, and why?  My take away from this thread is that a single report isn't usually enough to go searching hard for ways to tweak the documentation for readability; nor has this one pointed out any outright errors to be fixed.  In short, we expect that some subset of readers will ask us questions on the mailing list because that is the reality of things.

That said, I am curious as to the education flow a developer, not linking in this specific header to their code, would go through in order to learn about type OIDs and make use of them in their project.  Maybe that flow is good, maybe not.  It's a rare flow and there are many things to do in the project.  So my curiosity may not get satiated.  As you brought this up and are invested in the outcome you have more motivation than probably anyone else to dive into it and make concrete suggestions for change.

All that said, a comment at the top of what is probably the most important section of the header seems warranted.  Even if it is just mostly formality.  Mentioning the constant-ness of the integers should be part of that.

David J.

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 20 Mar 2025 18:48:27 +0100

> the "backward compatibility" aspect of this comment only meaningfully apply

That said, we could add a comment that makes this more obvious:

diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h
index ff666711a54..bce7d8796e2 100644
--- a/src/include/catalog/pg_type.h
+++ b/src/include/catalog/pg_type.h
@@ -341,6 +341,9 @@ MAKE_SYSCACHE(TYPENAMENSP, pg_type_typname_nsp_index, 64);
  */
 #define CASHOID	MONEYOID
 #define LSNOID	PG_LSNOID
+/*
+ * End of backwards compatibility section.
+ */
 
 #endif							/* EXPOSE_TO_CLIENT_CODE */

This looks a tad redundant in pg_type.h itself, but makes the generated
pg_type_d.h file more obvious:

/*
 * 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
/*
 * End of backwards compatibility section.
 */

#define BOOLOID 16
#define BYTEAOID 17

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"I must say, I am absolutely impressed with what pgsql's implementation of
VALUES allows me to do. It's kind of ridiculous how much "work" goes away in
my code.  Too bad I can't do this at work (Oracle 8/9)."       (Tom Allison)
           http://archives.postgresql.org/pgsql-general/2007-06/msg00016.php

---

From: Tom Lane <[email-masked]>
Date: Thu, 20 Mar 2025 14:54:04 -0400

> pg_type_d.h file more obvious:

I think it's a mistake to suppose that pg_type_d.h is the only
place where there's a risk of confusion.  We should be thinking
about this more generally: genbki.pl is taking zero thought to
make what it emits readable.  I think it would help to
label the sections it emits, perhaps along the lines of

/* Auto-generated OID macros */

for this part, and I'm not sure what other parts would be useful
to label.

As for CASHOID and LSNOID, surely those have been deprecated long
enough that we could just remove them?

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 20 Mar 2025 13:08:54 -0700

> for this part, and I'm not sure what other parts would be useful

I'd consider this enough for the moment, so long as we explicitly address
the cross-version constancy of the OID values associated with each type.

I think any other useful comments we'd want to include could be
sufficiently handled with one added general facility:

/*-------------------------------------------------------------------------
 *
 * %s
 *    %s
 *
 * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
 * Portions Copyright (c) 1994, Regents of the University of California
 *
 * NOTES
 *  ******************************
 *  *** DO NOT EDIT THIS FILE! ***
 *  ******************************
 *
 *  It has been GENERATED by src/backend/catalog/genbki.pl
 *
 *-------------------------------------------------------------------------
 * %s - have a spot in the *.h files to write some additional comments and
then inject them here if present
 */

I'm not going to dive deep enough to make more targeted suggestions.  It
does seem, though, that "client code" would seem mostly interested in these
OIDs and not stuff like the attribute numbers of the columns in pg_type.  I
get a distinct feel of one file serving multiple use cases.

> enough that we could just remove them?
I'd probably just leave them.

David J.