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

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?

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com