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

M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> writes:
> We are using PostgreSQL libpq in our application. The code worked fine for the past four years, but after upgrading
thelibrary, 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

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

Went I built from source in ended up in:

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/18/25 23:41, Sebastien Flaesch wrote:
> 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.

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.

> 
> Seb
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 3/18/25 23:41, Sebastien Flaesch wrote:
>> I was not expecting this file to be in a "server" folder, when it's to 
>> be used for client apps.

> Not surprising. As I understand it this is the code used to build the 
> 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

On Thu, Mar 20, 2025 at 8:18 AM Sebastien Flaesch
<sebastien.flaesch@4js.com> wrote:
>
> 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. 
>

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

Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> 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

On 3/20/25 00:17, Sebastien Flaesch wrote:
> 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
> 

You could borrow from here:

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

On Thu, Mar 20, 2025 at 4:43 PM Sebastien Flaesch
<sebastien.flaesch@4js.com> wrote:
> OR.... ( I guess I start to understand the code... ) it this comment only for:
> #define CASHOID MONEYOID
> #define LSNOID  PG_LSNOID

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

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

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.

On 2025-Mar-20, David G. Johnston wrote:

> 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.

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

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes:
> That said, we could add a comment that makes this more obvious:
> ...
> This looks a tad redundant in pg_type.h itself, but makes the generated
> 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

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Mar 20, 2025 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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 */

> 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.

That's documented elsewhere, I believe.  For the foo_d.h files,
I think it'd be sufficient to do something like 0001 attached.

Also, while checking out the results, I noticed that pg_class.h
has an "extern" in the wrong place: it's exposed to client code
which can have no use for it.  That extern doesn't mention any
backend-only typedefs, so it's fairly harmless, but it's still
a clear example of somebody not reading the memo.  Hence 0002.

> 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.

Well, yes it does, but that doesn't make those symbols useless to
client code.

            regards, tom lane

diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 2501307c92e..df3231fcd41 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -480,6 +480,8 @@ foreach my $catname (@catnames)
 
 EOM
 
+    printf $def "/* Macros related to the structure of $catname */\n\n";
+
     # Emit OID macros for catalog's OID and rowtype OID, if wanted
     printf $def "#define %s %s\n",
       $catalog->{relation_oid_macro}, $catalog->{relation_oid}
@@ -561,6 +563,7 @@ EOM
     print $def "\n#define Natts_$catname $attnum\n\n";
 
     # Emit client code copied from source header
+    printf $def "/* Definitions copied from ${catname}.h */\n\n";
     foreach my $line (@{ $catalog->{client_code} })
     {
         print $def $line;
@@ -573,6 +576,9 @@ EOM
         print $bki "open $catname\n";
     }
 
+    printf $def
+      "\n/* OID symbols for objects defined in ${catname}.dat */\n\n";
+
     # For pg_attribute.h, we generate data entries ourselves.
     if ($catname eq 'pg_attribute')
     {
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index fa96ba07bf4..07d182da796 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -231,8 +231,8 @@ MAKE_SYSCACHE(RELNAMENSP, pg_class_relname_nsp_index, 128);
      (relkind) == RELKIND_TOASTVALUE || \
      (relkind) == RELKIND_MATVIEW)

-extern int    errdetail_relkind_not_supported(char relkind);
-
 #endif                            /* EXPOSE_TO_CLIENT_CODE */

+extern int    errdetail_relkind_not_supported(char relkind);
+
 #endif                            /* PG_CLASS_H */

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Mar 20, 2025 at 2:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That's documented elsewhere, I believe.  For the foo_d.h files,
>> I think it'd be sufficient to do something like 0001 attached.

> WFM.  Thanks.

Thanks for looking at it.

>> Also, while checking out the results, I noticed that pg_class.h
>> has an "extern" in the wrong place: it's exposed to client code
>> which can have no use for it.  That extern doesn't mention any
>> backend-only typedefs, so it's fairly harmless, but it's still
>> a clear example of somebody not reading the memo.  Hence 0002.

> Maybe tack this onto genbki.h?

> diff --git a/src/include/catalog/genbki.h b/src/include/catalog/genbki.h
> index 26e205529d..4a1567a46b 100644
> --- a/src/include/catalog/genbki.h
> +++ b/src/include/catalog/genbki.h
> @@ -146,4 +146,6 @@
>   */
>  #undef EXPOSE_TO_CLIENT_CODE

> +/* Additional backend-only code is placed after the client-code section. */
> +
>  #endif                                                 /* GENBKI_H */

Doubt that would help ...

            regards, tom lane