Subject: Re: WIP: libpq: add a possibility to not send D(escribe) when executing a prepared statement

From: Ivan Trofimov <[email-masked]>
Date: Tue, 28 Nov 2023 13:18:57 +0300

Hi Tom! Thank you for considering this.

> It adds a whole new set of programmer-error possibilities, and I doubt
> it saves enough in typical cases to justify creating such a foot-gun.
Although I agree it adds a considerable amount of complexity, I'd argue 
it doesn't bring the complexity to a new level, since matching queries 
against responses is a concept users of asynchronous processing are 
already familiar with, especially so when pipelining is in play.

In case of a single-row select this can easily save as much as a half of 
the network traffic, which is likely to be encrypted/decrypted through 
multiple hops (a connection-pooler, for example), and has to be 
serialized/parsed on a server, a client, a pooler etc.
For example, i have a service which bombards its Postgres database with 
~20kRPS of "SELECT * FROM users WHERE id=$1", with "users" being a table 
of just a bunch of textual ids, a couple of timestamps and some enums in 
it, and for that service alone this change would save
~10Megabytes of server-originated traffic per second, and i have 
hundreds of such services at my workplace.

I can provide more elaborate network/CPU measurements of different 
workloads if needed.

> Instead, I'm tempted to suggest having PQprepare/PQexecPrepared
> maintain a cache that maps statement name to result tupdesc, so that
> this is all handled internally to libpq
 From a perspective of someone who maintains a library built on top of 
libpq and is familiar with other such libraries, I think this is much 
easier done on the level above libpq, simply because there is more 
control of when and how invalidation/eviction is done, and the level 
above also has a more straightforward way to access the cache across 
different asynchronous processing points.

> I just think that successful use of that option requires a client-
> side coding structure that allows tying a previously-obtained
> tuple descriptor to the current query with confidence. The proposed
> API fails badly at that, or at least leaves it up to the end-user
> programmer while providing no tools to help her get it right
I understand your concerns of usability/safety of what I propose, and I 
think I have an idea of how to make this much less of a foot-gun: what 
if we add a new function

PGresult *
PQexecPreparedPredescribed(PGconn *conn,
                            const char *stmtName,
                            PGresult* description,
                            ...);
which requires both a prepared statement and its tuple descriptor (or 
these two could even be tied together by a struct), and exposes its 
implementation (basically what I've prototyped in the patch) in the 
libpq-int.h?

This way users of synchronous API get a nice thing too, which is 
arguably pretty hard to misuse:
if the description isn't available upfront then there's no point to 
reach for the function added since PQexecPrepared is strictly better 
performance/usability-wise, and if the description is available it's 
most likely cached alongside the statement.
If a user still manages to provide an erroneous description, well,
they either get a parsing error or the erroneous description back,
I don't see how libpq could misbehave badly here.

Exposure of the implementation in the internal includes gives a 
possibility for users to juggle the actual foot-gun, but implies they 
know very well what they are doing, and are ready to be on their own.

What do you think of such approach?