Decoding PG custom types efficiently without OID collisions across databases

[jgaskins]

I'm working with a database that uses Postgres ENUM types extensively. They've been there for years and too many things depend on them at this point¹, so I can't replace them. Unfortunately, using non-core DB types (custom types and those provided by extensions) doesn't have first-class support yet in either db or this shard.

Part of it, for this shard at least, seems to be that we depend on the oid of the Postgres type to decode it, but the oid of non-core types (including via extensions that come with the core Postgres distribution) are usually different between databases. One way I've worked around this is to have the Crystal shard register a decoder with the correct oids after querying them from the specified DB. I don't know if I like that approach, though. Each oid must be unique across the entire application. If you connect to multiple databases (such as a primary and its replicas) and each one has a bunch of different extensions enabled, you may end up with collisions. That makes it impossible to use PG::Decoders::Decoder as the abstraction for decoding non-core types across those databases.

There have been several issues and PRs opened about using non-core types (many listed below, not exhaustive) and, when considering the above, I'm wondering if oid is the right abstraction for types with dynamic oids. The target Crystal type for the value is typically (always?) known at compile time, so we may simply be able to rely on ResultSet#read(TheType) to decode the value off the wire without considering the oid at all.

The main benefit of using PG::Decoders::Decoder is that you get direct access to the IO, which helps you avoid unnecessary heap allocations while decoding into the target type, but I think we can provide that directly to ResultSet#read, as well.

I have a proof of concept in this branch (which is a simplified version of the implementation I'm using in the app mentioned in the opening sentence), where I decode Postgres ENUM types into Crystal enum types directly from PG::ResultSet. The db shard already provides the ability to decode strings and integers from the DB into Crystal enum types but the approach in bbbcf16 (in that branch) is more efficient if the value is not stored as an integer².

The main thing to note in that branch is that ResultSet#read delegates everything to ResultSet#read(&), which yields io, bytesize, similar to how Decoder#decode receives io, bytesize, oid. This makes read(&) the most fundamental abstraction, even though most things still delegate to read.

Related to #250
Related to #115
Related to #63
Related to #88
Related to #43
Related to #221
Related to crystal-lang/crystal-db#8

A bunch of the issues above are closed, but the problem isn't really solved. We have workarounds for specific situations (and many of the issues listed above are also about specific situations), but no full solutions for the general problem. For example, decoding Postgres citext as a Crystal String works for deserializing text-like types into String, but involves an intermediate heap allocation for each value that is only used to create the String.

Since most of the above issues are about specific use cases, I wanted to open a discussion of the underlying problem. All previously proposed solutions (including my own) fall short in some way and I think the one I'm proposing here (implementing PG::ResultSet#read(&) as the base abstraction) might work in an oid-agnostic way. If not, I'd still like to try to find a generalizable solution.

Footnotes

1. I'd call them "load-bearing", but that's apparently become a signal that something was written by an LLM. The overuse of LLMs is ruining everything I like — including em dashes. Ugh. ↩

2. The branch also doesn't support decoding them from ints because it's intended for illustrative purposes rather than covering all possible bases. ↩

[jgaskins]

I also want to put together a list of necessary criteria for a general solution. For example:

• Avoids intermediate heap allocations
• Avoids oid collisions in Decoders when interacting with multiple databases
• Allows decoding a given Crystal type from any comparable Postgres type (for example, a Crystal String can be decoded from a surprising number of different Postgres types)

There may be other criteria that makes sense. This is just what rattles around in my head when I think about this problem.

[straight-shoota]

Sounds good.

I don't think we can ignore the OID entirely. It is important when a Crystal type maps to different postgres types (e.g. Time). That extends to custom types.

Most deserializations don't care about the wire representation. Even though String maps to multiple oids, the format is always the same.

Relevance is limited to a few types such as Time where we may encounter different wire formats.

So we may encounter a read for a Time type with an unknown (non-standard) oid of a custom data type.
Reading a Time needs type information to discern whether the timestamp is zoned or not.

Postgres exposes type information in the pg_type table.
So we could use that to query the base type, which tells how to read the wire format.

[jgaskins]

Yeah, I don't think we're going to be able to completely remove oid checks. In a lot of cases, using a Decoder based on the oid is also a fantastic abstraction. Converting PG::Numeric->BigDecimal is far easier to reason about than parsing straight off the wire into BigDecimal, for example.

Time is probably a great example of something that should stay in the Decoder suite.

[jgaskins]

ResultSet#read delegates everything to ResultSet#read(&), which yields io, bytesize, similar to how Decoder#decode receives io, bytesize, oid.

I should probably also clarify here that oid is available as a method on PG::ResultSet itself so it doesn't need to be yielded in read, but I just realized it's marked private, so it would only be available inside of ResultSet#read(YourType) methods. So we'd need to either consider the use cases a bit or just go ahead and yield oid anyway and the block can simply not receive it if it doesn't care about it.

[bcardiff]

The main benefit of using PG::Decoders::Decoder is that you get direct access to the IO, which helps you avoid unnecessary heap allocations while decoding into the target type, but I think we can provide that directly to ResultSet#read, as well.

The intermediate allocation also bothers me for IO based types, I would like to get rid of. Maybe the thing that you mention is missing directly in crystal-db to have more flexibility.

---

Supporting 1KB enum values seems a lot! 256 seems like a reasonable round number to me :-P

Is the string pool really needed? I would expect each enum type to handle that more gracefully...

[jgaskins]

Supporting 1KB enum values seems a lot! 256 seems like a reasonable round number to me :-P

It's absolutely overkill. 😄 I haven't checked what the maximum size of a Postgres enum value is (or if there is one) because the ones I've used in the past have typically been very small, so I threw something in there that should at least be larger than any reasonable value. If I could paste images into code, I would've replaced this comment with the following meme.

click for meme

Is the string pool really needed? I would expect each enum type to handle that more gracefully...

Not strictly necessary, no. However, Enum doesn't define a constructor that takes a slice, so we'd have to convert it to a String, which involves those intermediate heap allocations we want to avoid. I added the StringPool to amortize that cost. It's not great for small result sets, but

If you're open to accepting an Enum.parse(Bytes) constructor into the Crystal stdlib, I can do that so we can avoid thinking about this at all. 😄

Maybe the thing that you mention is missing directly in crystal-db to have more flexibility.

It would be amazing if we could do it there, tbh. I haven't looked at the other DB adapters yet and I wasn't sure whether DB::ResultSet, DB::Statement, and/or DB::Connection imposes the abstract methods needed to provide direct read access to the IO inside the ResultSet. I knew it could be done easily here, though.

[straight-shoota]

https://www.postgresql.org/docs/current/datatype-enum.html#DATATYPE-ENUM-IMPLEMENTATION-DETAILS

The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

So you're overkilling by 960 bytes 😆

Not strictly necessary, no. However, Enum doesn't define a constructor that takes a slice, so we'd have to convert it to a String, which involves those intermediate heap allocations we want to avoid. I added the StringPool to amortize that cost. It's not great for small result sets, but

If you're open to accepting an Enum.parse(Bytes) constructor into the Crystal stdlib, I can do that so we can avoid thinking about this at all. 😄

Yes, I think we need APIs that accept string-like data without requiring a String allocation.

[jgaskins]

in standard builds this means at most 63 bytes.

So you're overkilling by 960 bytes 😆

I take the "it's better to have too much than not enough" advice very seriously, apparently

Yes, I think we need APIs that accept string-like data without requiring a String allocation.

Fantastic, I'll put a PR together over there