Re: Proper query implementation for Postgresql driver

On 09/28/2014 05:53 PM, Shay Rojansky wrote:
> Hi.
>
> I'm a core developer on npgsql (the Postgresql .NET provider), we're
> hard at work on a major 3.0 version. I have a fundamental question
> that's been bugging me for a while and would like to get your opinion on it.
>
> Npgsql currently supports three basic query modes: simple, client-side
> parameters and prepared. The first two use the Postgresql simple query
> flow (client-side parameters means the user specifies parameters
> programmatically, just like with prepared queries, but the actual
> substitution work is done client-side). Prepared uses the Postgresql
> extended query flow.

Frankly, I suggest dropping "simple" entirely and using only the
parse/bind/describe/execute flow in the v3 protocol.

You can use this for server-side parameter binding *without* storing a
prepared statement by using unnamed statements.

Client-side parameter binding remains useful if you want to support
parameterisation where the PostgreSQL server its self does not, e.g. in
DDL. If you don't care about that, you could reasonably just drop client
side parameter support entirely.

> I would, in theory, love to switch the entire thing to binary and
> thereby avoid all textual parsing once and for all. If I understand
> correctly, this means all queries must be implemented as extended
> queries, with numerous extra client-server roundtrips - which are a bit
> hard to stomach.

What round-trips?

You can and should send parse/bind/describe/execute messages
back-to-back without waiting for a server response. Just Sync and wait
for server response at the end.

You can even send a parse then a stream of bind/describe/execute
messages for batch execution of a prepared statement against a list of
params, then a single Sync at the end.

> Can someone please let me know what the recommended/best practice here
> would be?

You might want to check out what PgJDBC does; it's fairly sane in this area.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services