Reading Postgres Types In Runtime

Sometimes, PG2 might perform its own queries to the database in the middle of your work. This is to obtain information about types: how to read and write values properly.

Postgres has a large variety of built-in types. Their numbers (also called OIDs) are known in advance because they're hardcoded. And when you query something, Postgres returns an integer OID of a column and raw bytes. By checking if this OID equals to 25 (text) or 23 (int4), you know how to handle these bytes.

Postgres never sends a type's name nor its schema. There is only an integer oid.

Situation gets hard when people install extensions or create types in runtime. Say, pg_vector, hstore, or ltree extensions introduce new types and their OIDs cannot be predicted. They are generated by Postgres. The same custom type may have OID = 15623 on your machine, 9623 on prod, 2345 in a Docker image and so on.

Even enum types suffer from the same issue. As they're created in runtime, one cannot predict their OID numbers.

Thus, PG2 has two internal maps: a global one with default OIDs and a local one which is special for each connection. Every time you query or execute something, the library checks if there are any unknown OIDs in the result. When there are, it peforms an extra query like this:

copy (
    select
        pg_type.oid,
        pg_type.typname,
        pg_type.typtype,
        pg_type.typinput::text,
        pg_type.typoutput::text,
        pg_type.typreceive::text,
        pg_type.typsend::text,
        pg_type.typarray,
        pg_type.typdelim,
        pg_type.typelem,
        pg_namespace.nspname
    from
        pg_type
    join
        pg_namespace on pg_type.typnamespace = pg_namespace.oid
    where
        pg_type.oid in (17760, 17762, ...)
) to stdout
    with (format binary)

Then, having all the information about an unknown type, it tries to find a guess a processor for this type. The data gets cached so when you peform the same query next time, the "unknown" types will be known, and there won't be an extra query.

So don't be surprised when you review SQL logs and see queries to the pg_type table. Unfortunately, there is no other way to fetch information about custom types. The official Postgres driver for JDBC does the same: it silently queries pg_type, pg_catalog and other tables in the middle of work.