Обсуждение: [libpq] OIDs of extension types? Of custom types?
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? Thanks for any insights on the above. --DD
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)