Re: A question for folks here

From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2024-07-16 13:34:40

On Tue, Jul 16, 2024 at 2:56 PM Mohammad Nejati [ashtum] via Boost
<boost_at_[hidden]> wrote:
> On Tue, Jul 16, 2024 at 3:47 PM Ruben Perez <rubenperez038_at_[hidden]> wrote:
> > Yes! That's it. Actually, the mapping is one of the points I don't see any clearly after reading the protocol spec (and it looks like libpq doesn't solve it either, leaving you just a bunch of bytes/text to interpret). Do you have any ideas or any resources on how to approach the issue?
>
> I found what is done in https://github.com/yandex/ozo inspiring. It
> creates a compile-time map for standard types with fixed OIDs and a
> runtime map for user-defined types, retrieving OID numbers from
> PostgreSQL upon first use. Additionally, it exposes several type
> traits for specialization, enabling the use of containers and wrapper
> types for PostgreSQL types like

Interesting, thanks, I wasn't aware of it. I'll need to check it out.

I think I have something similar for the fixed compile-time OIDs in my
home-grown libpq wrapper.
Thanks to those, I can manipulate basic types easily for binding and
retrieval in a typesafe manner,
with runtime checks to validate actual and expected OIDs match.

In my use case, I do everything using the BINARY format for binding
and result-sets.
And I use the COPY protocol extensively for performance (also in
BINARY format), in both directions.

I'm no high-caliber C++ dev, but I'm sharing below an excerpt of what
I do, in case that's useful.
There's more, but this is not the time nor place.

For another interesting C++ libpq wrapper, also check
https://github.com/dmitigr/pgfe

Thanks, --DD

PS: I'd appreciate if you can advertise where you work I github, so I
can monitor it, please

namespace pq {
...
// "Strong-typedefs", to prevent implicit conversions
enum class Oid : uint32_t {};
enum class Row : uint32_t {};
enum class Col : uint32_t {};

inline constexpr Oid cInvalidOid{0};
...
static_assert(std::is_same_v<std::underlying_type_t<Oid>, ::Oid>);

// Immutable "views" over "typed" (in the SQL sense) memory
using Bytea = std::span<const std::byte>;
using Name  = std::span<const char, 63>;
using Text  = std::string_view;
using Uuid  = std::span<const std::byte, 16>;
...
struct Type {
    const Text name_;
    const int len_ = -1;
    const Oid oid_ = cInvalidOid;
    const Oid array_oid_ = cInvalidOid;
};
...
template <typename T, typename Enable = void>
struct OidTraits {
    // fail at compile-time, not link-time. (see http://goo.gl/mPTYn)
    static_assert(sizeof(typename T::please_specialize_OidTraits) == 0);
};

// For full catalog of available OIDs in PostgreSQL, see
// https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
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 } };
};

template<> struct OidTraits<char> {
    // single character
    static constexpr Type type{ "char", 1, Oid{ 18 }, Oid{ 1002 } };
};

template<> struct OidTraits<Name> {
    // 63-byte type for storing system identifiers
    static constexpr Type type{ "name", int(Name::extent), Oid{ 19 },
Oid{ 1003 } };
};

template<> struct OidTraits<int64_t> {
    // ~18 digit integer, 8-byte storage
    static constexpr Type type{ "int8", 8, Oid{ 20 }, Oid{ 1016 } };
};

template<> struct OidTraits<int16_t> {
    // 32 thousand to 32 thousand, 2-byte storage
    static constexpr Type type{ "int2", 2, Oid{ 21 }, Oid{ 1005 } };
};

using Int2Vector = std::span<const int16_t>;
template<> struct OidTraits<Int2Vector> {
    // array of int2, used in system tables
    static constexpr Type type{ "int2vector", -1, Oid{ 22 }, Oid{ 1006 } };
};

template<> struct OidTraits<int32_t> {
    // -2 billion to 2 billion integer, 4-byte storage
    static constexpr Type type{ "int4", 4, Oid{ 23 }, Oid{ 1007 } };
};

// Skipping regproc (24, 1008) registered procedure

template<> struct OidTraits<Text> {
    // variable-length string, no limit specified
    static constexpr Type type{ "text", -1, Oid{ 25 }, Oid{ 1009 } };
};

template<> struct OidTraits<std::string> : OidTraits<Text> {};

template<> struct OidTraits<Oid> {
    // object identifier(oid), maximum 4 billion
    static constexpr Type type{ "oid", 4, Oid{ 26 }, Oid{ 1028 } };
};

template<> struct OidTraits<float> {
    // single-precision floating point number, 4-byte storage
    static constexpr Type type{ "float4", 4, Oid{ 700 }, Oid{ 1021 } };
};

template<> struct OidTraits<double> {
    // double-precision floating point number, 8-byte storage
    static constexpr Type type{ "float8", 8, Oid{ 701 }, Oid{ 1022 } };
};

// TODO: bpchar(1042, 1014): char(length), blank-padded string, fixed
storage length
// TODO: varchar(1043, 1015): varchar(length), non-blank-padded
string, variable storage length

// TODO: date(1082, 1182): date
// TODO: time(1083, 1183): time of day

/*
** TODO: How to map the same C++ type to a different SQL type?
template<> struct OidTraits<Timestamp> {
    // date and time
    static constexpr Type type{ "timestamp", 8, Oid{ 1114 }, Oid{ 1115 } };
};
*/

template<> struct OidTraits<Timestamp> {
    // date and time with time zone
    static constexpr Type type{ "timestamptz", 8, Oid{ 1184 }, Oid{ 1185 } };
};

// For wire-representations of those datatypes, see below (and co.)
// https://github.com/postgres/postgres/blob/master/src/include/utils/date.h

template<> struct OidTraits<Uuid> {
    // UUID datatype
    static constexpr Type type{ "uuid", 16, Oid{ 2950 }, Oid{ 2951 } };
};

template<> struct OidTraits<
    acme::core::Guid
> : OidTraits<
    Uuid
> {};

// Partial specialization for all enums
// Warning: Unsigned integer underlying types not supported by PostgreSQL
template <typename E> struct OidTraits<
    E, std::enable_if_t<std::is_enum_v<E>>
> : OidTraits<
    std::underlying_type_t<E>
> {};

//============================================================================

template <typename T>
constexpr Oid oid_of() {
    if constexpr (std::is_array_v<T>) {
        static_assert(std::rank<T>::value == 1);
        using elem_t = typename std::remove_all_extents<T>::type;
        return OidTraits<elem_t>::type.array_oid_;
    } else {
        return OidTraits<T>::type.oid_;
    }
}

template <typename T>
constexpr const Type& type_of() {
    return OidTraits<T>::type;
}

inline
void ensure_oid(Oid actual, Oid expected) {
    if (actual != expected) {
        throw std::runtime_error(fmt::format(
            "Unexpected Oid: Got {}; Want {}", ::Oid(actual), ::Oid(expected)
        ));
    }
}

template <typename T>
void ensure_oid(Oid actual) {
    ensure_oid(actual, oid_of<T>());
}
...
}