Re: Proper query implementation for Postgresql driver

Thanks for the answer Craig.

The remaining point of confusion is not really about simple vs. extended,
it's about text vs. binary. Sending parse/bind/describe/execute messages
back-to-back means the results have to be all text or all binary.

So the question is: are we supposed to transfer all types to and from the
backend in binary? If so, that raises some difficulties (see my previous
message) which I wanted to get your opinion on.

Thanks for the suggestion to look at PgJDBC, I'll do that.

Shay

On Tue, Sep 30, 2014 at 7:20 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> 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
>