[libpq] OIDs of extension types? Of custom types?

On Fri, 2022-10-14 at 13:39 +0200, Dominique Devienne wrote:

Hi. I'm using binary binds and results for DMLs, as well as for COPY.

So far, I've stayed within built-in scalar and array types, thus I
could hardcode the OIDs of values "in the type-system" (in C++, via
traits).
But I'd like to venture into extension (e.g. hstore) and custom
(enums, domain, etc...) types.
Thus I'm wondering:
1) whether "official" extensions have fixed/stable OIDs, like in my
hstore example. If so, where are they defined?
2) how should I be looking up OIDs for custom (or extension?) types
with libpq? Any specific APIs? Or I need to do SQL instead?
3) If I duplicate custom types per-schema, to keep them standalone,
they'll get different OIDs, right?

You use the #defines like TEXTOID for the built-in Oids, right?

For types from an extensions, you would run a query on "pg_type".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Fri, Oct 14, 2022 at 2:31 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

You use the #defines like TEXTOID for the built-in Oids, right?

I don't. I used
https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
as a reference.
I suspect that should be fairly stable, right? I have at least 2 or 3
dozen OIDs pairs (scalar + array) of primitives
and other types (Oid, Name, Text, Bytea, Uuid, etc...). Are there
#defines for all of those? Where?

template<> struct OidTraits<bool> {
// boolean, true/false
static constexpr Type type{ "bool", 1, Oid{ 16 }, Oid{ 1000 } };
};

template<> struct OidTraits<Bytea> {
// variable-length string, binary values escaped
static constexpr Type type{ "bytea", -1, Oid{ 17 }, Oid{ 1001 } };
};

etc...

For types from an extensions, you would run a query on "pg_type".

OK, thanks.

Dominique Devienne <ddevienne@gmail.com> writes:

On Fri, Oct 14, 2022 at 2:31 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

You use the #defines like TEXTOID for the built-in Oids, right?

I don't. I used
https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
as a reference.
I suspect that should be fairly stable, right? I have at least 2 or 3
dozen OIDs pairs (scalar + array) of primitives
and other types (Oid, Name, Text, Bytea, Uuid, etc...). Are there
#defines for all of those? Where?

They're stable, but writing magic numbers leads to unreadable code.
Use the macros from catalog/pg_type_d.h.

For types from an extensions, you would run a query on "pg_type".

OK, thanks.

In SQL queries, you can avoid hard-wiring anything by writing
things like "'hstore'::regtype". It may or may not be possible
to avoid fetching the OID altogether that way.

regards, tom lane

On Fri, Oct 14, 2022 at 4:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I don't. I used
https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat

They're stable

Good to know, thanks.

but writing magic numbers leads to unreadable code.
Use the macros from catalog/pg_type_d.h.

OK. I see that header. But it seems to be a "server" header, not a client one.
I.e. I'm not sure it's a good idea to depend on such a header for pure
client-side libpq code.

For types from an extensions, you would run a query on "pg_type".

OK, thanks.

In SQL queries, you can avoid hard-wiring anything by writing
things like "'hstore'::regtype". It may or may not be possible
to avoid fetching the OID altogether that way.

Something like below you mean?
Thanks for the tip.

I do need to OIDs, on binds and results (defines in OCI speak), in my C++ code.
Because I try to enforce strong type safety between the C++ world, and
the libpq "bytes".
I don't check OIDs of the values on binds, I just give them to libpq
on execute().
But for results, I compare the actual OID (from PGresult) and the
expected OID from the C++ type (via the traits).

ddevienne=> select 'hstore'::regtype;
regtype
---------
hstore
(1 row)

ddevienne=> select 'hstore'::regtype::oid;
oid
-----------
207025799
(1 row)

ddevienne=> select 'uuid'::regtype::oid;
oid
------
2950
(1 row)

ddevienne=> select 'uuid[]'::regtype::oid;
oid
------
2951
(1 row)