Message-ID: <606af1a2-07d6-fdef-b160-97037db59591@yandex.ru>
Date: Tue, 28 Nov 2023 13:18:57 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Subject: Re: WIP: libpq: add a possibility to not send D(escribe) when
 executing a prepared statement
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Andrey M. Borodin" <x4mmm@yandex-team.ru>,
 pgsql-hackers@lists.postgresql.org
References: <82c438b4-d91c-9009-65fc-593124d5a277@yandex.ru>
 <6E9981D0-F66B-4102-8D6E-E85C7F2BD77B@yandex-team.ru>
 <2b9c6c1f-d5fc-cc29-c387-95abb815d12d@yandex.ru>
 <3666238.1701018825@sss.pgh.pa.us>
From: Ivan Trofimov <i.trofimow@yandex.ru>
In-Reply-To: <3666238.1701018825@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/606af1a2-07d6-fdef-b160-97037db59591%40yandex.ru>
Precedence: bulk

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?