Re: Why does the prepareThreshold=0 (to cover pgbouncer transaction mode) disables protocol binary transfers ? (w/ PoC patch and measurements)

On Mon, Apr 25, 2022 at 1:08 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 2022-04-22 Fr 14:05, David G. Johnston wrote:
> > On Fri, Apr 22, 2022 at 7:06 AM Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)com>
> > wrote:
> >
> > prepareThreshold=0
> >
> >
> > i.e., you are preventing the use of prepared statements being sent
> > from the client to the server.
> >
> > As a first point of order, the PostgreSQL "Simple Query Protocol" [1]
> > says:
> >
> > "In simple Query mode, the format of retrieved values is always text,
> > except when the given command is a FETCH from a cursor declared with
> > the BINARY option."
> >
> > It appears as though setting prepareThreshold=0 causes the driver to
> > use the Simple Query Protocol.
> >
> > By forcing binary transfer you override prepareThreshold=0 and use a
> > prepared statement anyway because it is only possible to get the
> > binary data via the Extended Query Protocol (parse, bind, execute).
> >
> > My understanding is that "Prepared Statements" is simply a different
> > name for "Extended Query Protocol".
> >
> > Where I think JDBC (and maybe pgbouncer) is going wrong with this, is
> > they don't make (allow for) proper use of the "unnamed prepared
> > statement" which: "...lasts only until the next Parse statement
> > specifying the unnamed statement as destination is issued." [2] The
> > code does have oneShot in the parse/bind/execute path so it is
> > recognized...at least in JDBC.
> >
> > By using the unnamed prepared statement you get easy use of the
> > Extended Query Protocol without having to retain any meaningful state
> > which can be messed up if improperly shared. pgbouncer, in
> > transaction mode, should just enforce "Parse/Bind/Execute <unnamed>"
> > and then get out of the way. The JDBC can provide whatever friendly
> > UX it wants so long as the user can specify that they don't care about
> > caching and only want to use the unnamed prepared statement.
> >
> > [1]
> > https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.5.7.4
> > [2]
> >
> https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
> >
> > Hopefully the above helps somewhat. I tried getting my head around
> > the JDBC end of this and it seems like they do have some provisions
> > for it - whether they are sufficient or user-friendly is another matter.
> >
> > You need to get your query into the "parse/bind/execute" flow path AND
> > have it return true for oneShot; this will prevent a name from being
> > assigned to the "prepared statement" and thus the dynamic <unnamed>
> > one will be used for all three stages, and then whatever query comes
> > along next can just do the same thing. I would expect that to just
> > work so far as usage of binary data for the UUID data goes.
> >
> > I haven't done any tests, just some code review.
> >
> >
>
> How's that going to work if you have two prepared statements and want to
> execute them in an interleaved fashion? How is the driver meant to know
> when to use the unnamed statement and when not to?
>
>
Session configuration. If you are using a pooler mode that simply doesn't
play nice with named prepared statements you must configure JDBC to not use
them (ever) and use only the unnamed prepared statement for
parse/bind/execute. The interleaving you want to do is simply not possible
(or, rather, you will not get the benefit of actually having the prepared
statement saved in a cache for re-use, it will be re-parsed every time).

This seems better than nothing given lots of uses of prepared statements
are simply to get access to the extended query
protocol's parse/bind/execute. It is quite possible an even better
solution exists if pgJDBC and pgbouncer cooperate and design and implement
something to overcome this complaint. I'm not going there myself (not that
I'm implementing this suggestion) as this solution seems simpler and
sufficiently effective for the majority of use cases. It also likely
doesn't impose any kind of constraints (especially as this is basically
implementation details + configuration, not code-level API changes) on a
more nuanced solution.

David J.