public inbox for [email protected]help / color / mirror / Atom feed
Re: After upgrading libpq, the same function(PQftype) call returns a different OID 17+ messages / 7 participants [nested] [flat]
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-16 09:30 Sebastien Flaesch <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Sebastien Flaesch @ 2025-03-16 09:30 UTC (permalink / raw) To: Tom Lane <[email protected]>; M Tarkeshwar Rao <[email protected]>; +Cc: pgsql-general 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 protected]> Sent: Saturday, March 15, 2025 8:02 PM To: M Tarkeshwar Rao <[email protected]> Cc: [email protected] <[email protected]> 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 protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-18 18:41 Adrian Klaver <[email protected]> parent: Sebastien Flaesch <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Adrian Klaver @ 2025-03-18 18:41 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; Tom Lane <[email protected]>; M Tarkeshwar Rao <[email protected]>; +Cc: pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-19 06:41 Sebastien Flaesch <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Sebastien Flaesch @ 2025-03-19 06:41 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; Tom Lane <[email protected]>; M Tarkeshwar Rao <[email protected]>; +Cc: pgsql-general 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 protected]> Sent: Tuesday, March 18, 2025 7:41 PM To: Sebastien Flaesch <[email protected]>; Tom Lane <[email protected]>; M Tarkeshwar Rao <[email protected]> Cc: [email protected] <[email protected]> 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. 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://urldefense.com/v3/__https://www.postgresql.org/docs/17/libpq-exec.html*LIBPQ-PQFTYPE__;Iw!!I... > 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 protected] ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-19 16:53 Adrian Klaver <[email protected]> parent: Sebastien Flaesch <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Adrian Klaver @ 2025-03-19 16:53 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; Tom Lane <[email protected]>; M Tarkeshwar Rao <[email protected]>; +Cc: pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-19 17:22 Tom Lane <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Tom Lane @ 2025-03-19 17:22 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Sebastien Flaesch <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general Adrian Klaver <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 07:17 Sebastien Flaesch <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 3 replies; 17+ messages in thread From: Sebastien Flaesch @ 2025-03-20 07:17 UTC (permalink / raw) To: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; +Cc: M Tarkeshwar Rao <[email protected]>; pgsql-general 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 protected]> Sent: Wednesday, March 19, 2025 6:22 PM To: Adrian Klaver <[email protected]> Cc: Sebastien Flaesch <[email protected]>; M Tarkeshwar Rao <[email protected]>; [email protected] <[email protected]> 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. Adrian Klaver <[email protected]> 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://urldefense.com/v3/__https://www.postgresql.org/docs/devel/bki.html__;!!I_DbfM1H!GM5pJKRPNVAr... >> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 10:47 Luca Ferrari <[email protected]> parent: Sebastien Flaesch <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: Luca Ferrari @ 2025-03-20 10:47 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; +Cc: pgsql-general On Thu, Mar 20, 2025 at 8:18 AM Sebastien Flaesch <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 14:31 Tom Lane <[email protected]> parent: Sebastien Flaesch <[email protected]> 2 siblings, 1 reply; 17+ messages in thread From: Tom Lane @ 2025-03-20 14:31 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; +Cc: Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general Sebastien Flaesch <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 15:33 Adrian Klaver <[email protected]> parent: Sebastien Flaesch <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: Adrian Klaver @ 2025-03-20 15:33 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; Tom Lane <[email protected]>; +Cc: M Tarkeshwar Rao <[email protected]>; pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 15:42 Sebastien Flaesch <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 17+ messages in thread From: Sebastien Flaesch @ 2025-03-20 15:42 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general 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: /* * 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. 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 protected]> Sent: Thursday, March 20, 2025 3:31 PM To: Sebastien Flaesch <[email protected]> Cc: Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; [email protected] <[email protected]> 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. Sebastien Flaesch <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 15:49 Dominique Devienne <[email protected]> parent: Sebastien Flaesch <[email protected]> 1 sibling, 1 reply; 17+ messages in thread From: Dominique Devienne @ 2025-03-20 15:49 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general On Thu, Mar 20, 2025 at 4:43 PM Sebastien Flaesch <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 16:25 David G. Johnston <[email protected]> parent: Sebastien Flaesch <[email protected]> 1 sibling, 2 replies; 17+ messages in thread From: David G. Johnston @ 2025-03-20 16:25 UTC (permalink / raw) To: Sebastien Flaesch <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general On Thu, Mar 20, 2025 at 8:42 AM Sebastien Flaesch <[email protected]> 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. > > OR.... ( I guess I start to understand the code... ) it this comment only > for: > 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 > as PG_TYPE_) > 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 > > 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. ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 16:40 Sebastien Flaesch <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 0 replies; 17+ messages in thread From: Sebastien Flaesch @ 2025-03-20 16:40 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general Dominique, That's what Tom already replied, yes. --DD My bad! I missed that answer from Tom. Thanks. Seb ________________________________ From: Dominique Devienne <[email protected]> Sent: Thursday, March 20, 2025 4:49 PM To: Sebastien Flaesch <[email protected]> Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; [email protected] <[email protected]> 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. On Thu, Mar 20, 2025 at 4:43 PM Sebastien Flaesch <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 17:24 Sebastien Flaesch <[email protected]> parent: David G. Johnston <[email protected]> 1 sibling, 0 replies; 17+ messages in thread From: Sebastien Flaesch @ 2025-03-20 17:24 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general 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__si... 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 protected]> Sent: Thursday, March 20, 2025 5:25 PM To: Sebastien Flaesch <[email protected]> Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; [email protected] <[email protected]> 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. On Thu, Mar 20, 2025 at 8:42 AM Sebastien Flaesch <[email protected]<mailto:[email protected]>> 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. OR.... ( I guess I start to understand the code... ) it this comment only for: 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 as PG_TYPE_) 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 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. ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 17:48 Álvaro Herrera <[email protected]> parent: David G. Johnston <[email protected]> 1 sibling, 1 reply; 17+ messages in thread From: Álvaro Herrera @ 2025-03-20 17:48 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Sebastien Flaesch <[email protected]>; Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 18:54 Tom Lane <[email protected]> parent: Álvaro Herrera <[email protected]> 0 siblings, 1 reply; 17+ messages in thread From: Tom Lane @ 2025-03-20 18:54 UTC (permalink / raw) To: Álvaro Herrera <[email protected]>; +Cc: David G. Johnston <[email protected]>; Sebastien Flaesch <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general =?utf-8?Q?=C3=81lvaro?= Herrera <[email protected]> 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 ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After upgrading libpq, the same function(PQftype) call returns a different OID @ 2025-03-20 20:08 David G. Johnston <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 17+ messages in thread From: David G. Johnston @ 2025-03-20 20:08 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Sebastien Flaesch <[email protected]>; Adrian Klaver <[email protected]>; M Tarkeshwar Rao <[email protected]>; pgsql-general On Thu, Mar 20, 2025 at 11:54 AM Tom Lane <[email protected]> wrote: > =?utf-8?Q?=C3=81lvaro?= Herrera <[email protected]> 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. > 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. > As for CASHOID and LSNOID, surely those have been deprecated long > enough that we could just remove them? > > I'd probably just leave them. David J. ^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2025-03-20 20:08 UTC | newest] Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-16 09:30 Re: After upgrading libpq, the same function(PQftype) call returns a different OID Sebastien Flaesch <[email protected]> 2025-03-18 18:41 ` Adrian Klaver <[email protected]> 2025-03-19 06:41 ` Sebastien Flaesch <[email protected]> 2025-03-19 16:53 ` Adrian Klaver <[email protected]> 2025-03-19 17:22 ` Tom Lane <[email protected]> 2025-03-20 07:17 ` Sebastien Flaesch <[email protected]> 2025-03-20 10:47 ` Luca Ferrari <[email protected]> 2025-03-20 14:31 ` Tom Lane <[email protected]> 2025-03-20 15:42 ` Sebastien Flaesch <[email protected]> 2025-03-20 15:49 ` Dominique Devienne <[email protected]> 2025-03-20 16:40 ` Sebastien Flaesch <[email protected]> 2025-03-20 16:25 ` David G. Johnston <[email protected]> 2025-03-20 17:24 ` Sebastien Flaesch <[email protected]> 2025-03-20 17:48 ` Álvaro Herrera <[email protected]> 2025-03-20 18:54 ` Tom Lane <[email protected]> 2025-03-20 20:08 ` David G. Johnston <[email protected]> 2025-03-20 15:33 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox