Thread: Extend libpq to support mixed text and binary results

The Frontend/Backend protocol's "bind" message allows to
specify mixed text and binary result formats, but there
is not support in the C API for that.

I'd like to add support for that to libpq for these reasons:

- I think that every feature of the line protocol should
  be exposed in the C API.

- This is quite useful if you select bytea and other
  fields in one query (less overhead and data traffic
  for the bytea).

- I've seen at least one case on the list where
  somebody would have liked to have that feature:
  http://archives.postgresql.org/pgsql-general/2012-08/msg00761.php

Attached is a WIP patch that adds four new functions
to libpq:
PQexecParams2
PQexecPrepared2
PQsendQueryParams2
PQsendQueryPrepared2

If that idea meets with approval, I'd add documentation and
submit it to the next commitfest.

I am aware that these names are rather ugly and welcome
better ideas.

Maybe it is not necessary to add four new functions.
You could get the full functionality just with
PQsendQueryPrepared2, the rest is just for ease of use.

Yours,
Laurenz Albe

On 7 November 2012 13:08, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

> - I think that every feature of the line protocol should
>   be exposed in the C API.

Exposing every possible bug in ther underlying protocol isn't the best
plan though, especially when doing so complicates the API just to
support this.

Are those the only reasons you want this? They seem pretty thin set of
requirements to me that will lead to dead and hard to test code.

Or perhaps associated measurements are needed to show clear benefit?
i.e. please convince somre more.

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

On Wed, Nov 7, 2012 at 7:08 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> The Frontend/Backend protocol's "bind" message allows to
> specify mixed text and binary result formats, but there
> is not support in the C API for that.
>
> I'd like to add support for that to libpq for these reasons:
>
> - I think that every feature of the line protocol should
>   be exposed in the C API.
>
> - This is quite useful if you select bytea and other
>   fields in one query (less overhead and data traffic
>   for the bytea).
>
> - I've seen at least one case on the list where
>   somebody would have liked to have that feature:
>   http://archives.postgresql.org/pgsql-general/2012-08/msg00761.php
>
> Attached is a WIP patch that adds four new functions
> to libpq:
> PQexecParams2
> PQexecPrepared2
> PQsendQueryParams2
> PQsendQueryPrepared2
>
> If that idea meets with approval, I'd add documentation and
> submit it to the next commitfest.
>
> I am aware that these names are rather ugly and welcome
> better ideas.
>
> Maybe it is not necessary to add four new functions.
> You could get the full functionality just with
> PQsendQueryPrepared2, the rest is just for ease of use.

How would this compare to libpqtypes which I might in-humbly suggest
is the gold standard in terms of dealing with libpq binary formats: it
pulls everything in binary and puts the complicated stuff in structs
so you don't have to parse.

merlin

Simon Riggs <simon@2ndQuadrant.com> writes:
> On 7 November 2012 13:08, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> - I think that every feature of the line protocol should
>> be exposed in the C API.

My recollection is that there are several things in the V3 protocol that
were put there to satisfy JDBC, without any expectation that they'd
necessarily be useful to libpq.  So I don't buy the above argument as-is;
it requires some clear use-cases for libpq.

> Exposing every possible bug in ther underlying protocol isn't the best
> plan though, especially when doing so complicates the API just to
> support this.

The fact that you couldn't think of any function names more mnemonic
than "PQexecParams2" etc strongly suggests that this is mostly
complication :-(

The referenced thread from August doesn't strike me as sufficient
justification.  In the first place, that's only one request, and in the
second place, that user was going through Perl DBI.  I don't really
foresee enough changes being made to the DBI/DBD stack to be able to use
this facility effectively.  The key point there (which is a deficiency
the JDBC folk have already complained about) is that this design only
helps you if you know *in advance of running the query* which result
columns are bytea.  While that's not a serious problem for applications
running hard-coded queries, client-side libraries aren't going to know
that unless they incur extra network round-trips to ask the server to
Describe the query result.

So what I think would be considerably more useful than this is some sort
of session-level setting saying "please send results of such-and-such
data type(s) in binary/text automatically".  Not sure we can have that
without a protocol break though.
        regards, tom lane

Simon Riggs wrote:
>> - I think that every feature of the line protocol should
>>   be exposed in the C API.
>
> Exposing every possible bug in ther underlying protocol isn't the best
> plan though, especially when doing so complicates the API just to
> support this.

Well, I wouldn't call this a bug, but I got enough good
points against the idea that I consider it dead.

Yours,
Laurenz Albe