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

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.

David J.