Text protocol support ? What are the downsides?

[Weakky]

Context

(If you wanna get straight to the point, you can skip this context and read the second part)

Hey, so we've been using tokio-postgres at Prisma for quite a while now. Very briefly, Prisma is a JavaScript ORM-like with a backend in rust. We use rust for various reasons that aren't relevant to my question here.

The ability for our users to send "raw" queries is an essential escape hatch. Unfortunately, we're seeing quite a lot of our users confused as to why they're unable to query a bunch of PG types that Prisma doesn't officially support, even with raw queries. eg: PostGIS types. They're especially confused because 99.9% of JS ORMs are based on the node-postgres driver, which uses the text protocol under the hood and so these queries fetching custom types used to work for them.

The underlying reason, obviously, is that we don't have ToSql/FromSql traits implemented for those types.

This leaves us with two options:

1. Suggest them to TEXT cast those types if they wish to query them
   • 🟡 That's an ok workaround but that prevents them from SELECT *.
2. Gradually support more types
   • 🔴 We need those escape hatch to work reliably without us intervening, otherwise we'd just officially support those types.

It might be possible to just forward those bytes to the Javascript side and enable users to parse them themselves, but the binary representation makes it much harder for common users. Worse, it seems like the best existing documentation as to how complex types should be deserialized is by reading the PG codebase, which is not amazing from a DX point of view when you're just willing to execute a query.

What would be much more approachable for our users is to receive a text representation for those unsupported types which they can easily parse if they wish so. In a lot of cases, it is hacky and/or inefficient, but that's how the JS community does it already anyway.

This brings me to my question 👇🏻

Text protocol, what are the downsides?

We've recently investigated the PG protocol in the hope of finding downsides to the text protocol, but we haven't really. The extended query flow documentation says types can individually be requested either as string or as binary (and this discussion too).

Now, as the title asks, what are the downsides of using the text protocol, and why does rust-postgres prefer using the binary protocol? Are there common PG types that lack a text representation? Does it lead to type ambiguities in some cases? Does it have performance implications?

If there are no clear downsides though, is there any chance you'd be ok having rust-postgres support that protocol?

Thanks for your time, cheers

[sfackler]

The binary protocol is significantly easier to work with if you want to parse out Rust types, which is what the library is primarily designed around.

The simple_query interface will return strings, but it doesn't support query parameters which may not be sufficient for what you're looking for.

It definitely seems reasonable to expose text-encoded query parameters and return values, but the API design will be somewhat awkward. In particular, you need to specify the encoding of return values during statement preparation, before you have access to the FromSql implementations. One option would be to just have an AIP that works purely in text mode for all parameters and return values but that does leave some flexibility on the table.

[tobyhede]

The Binary format is not actually recommended:

Binary representations for integers use network byte order (most significant byte first). For other data types consult the documentation or source code to learn about the binary representation. Keep in mind that binary representations for complex data types might change across server versions; the text format is usually the more portable choice.

https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-FORMAT-CODES

Most other drivers seem to default to TEXT or enable the format to be selected.