Re: [HACKERS] Multiple queries in transit

2011-11-06 Thread Jeroen Vermeulen

On 2011-11-03 17:26, Marko Kreen wrote:

On Mon, Oct 31, 2011 at 7:09 PM, Tom Lanet...@sss.pgh.pa.us  wrote:

Can't you do that today with a multi-command string submitted to
PQsendQuery, followed by multiple calls to PQgetResult?


It's more annoying to to error handling on that, plus it still keeps the
blocking behaviour, just with larger blocks.


You can combine multi-command query strings with nonblocking mode, 
without any change in libpq itself.


In fact that's exactly what the libpqxx pipeline class does.  So if 
you're working in C++, you already have this feature at your disposal.




Also I would ask for opposite feature: multiple rows in flight.
That means that when server is sending big resultset,
the app can process it row-by-row (or by 10 rows)
without stopping the stream and re-requesting.


Cursors.


Jeroen

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-11-03 Thread Marko Kreen
On Mon, Oct 31, 2011 at 7:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 31.10.2011 17:44, Mark Hills wrote:
 Could libpq be reasonably modified to allow this?

 I believe it's doable in theory, no-one has just gotten around to it.
 Patches are welcome.

 Can't you do that today with a multi-command string submitted to
 PQsendQuery, followed by multiple calls to PQgetResult?

It's more annoying to to error handling on that, plus it still keeps the
blocking behaviour, just with larger blocks.

 I'm hesitant to think about supporting the case more thoroughly than
 that, or with any different semantics than that, because I think that
 the error-case behavior will be entirely unintelligible/unmaintainable
 unless you abandon all queries-in-flight in toto when an error happens.
 Furthermore, in most apps it'd be a serious PITA to keep track of which
 reply is for which query, so I doubt that such a feature is of general
 usefulness.

Thats why query queue and error handling must happen in protocol
library, not app.  And it seems doable, unless the server eats
queries or errors in some situation, breaking simple sequential
query-response mapping.  Do you know of such behaviour?

(And several queries in Simple Queriy are known exception,
we can ignore them here.)


Also I would ask for opposite feature: multiple rows in flight.
That means that when server is sending big resultset,
the app can process it row-by-row (or by 10 rows)
without stopping the stream and re-requesting.

-- 
marko

PS. I think full-duplex is better than pipeline here, latter
seems to hint something unidirectional, except yeah,
it is used in HTTP 1.1 for similar feature.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Heikki Linnakangas

On 31.10.2011 17:44, Mark Hills wrote:

We have a user interface which fetches and displays many small pieces of
distinct information from a PostgreSQL database.

* fetches are simple lookups across a diverse set of tables,
   in response to events on another data source

* uses PQsendQuery() on a non-blocking socket

But data fetches visibly take some time -- libpq doesn't allow a second
query to be sent until the first has been fully processed. The
back-and-forth seems to give a bottleneck on the round-trip.

Instead, it would be preferable to send multiple requests (down the TCP
socket), and then receive multiple responses (in order).

This would allow the sending, processing and receiving response to be
interleaved much more reasonably, and reduce the delay.

Could libpq be reasonably modified to allow this?


I believe it's doable in theory, no-one has just gotten around to it. 
Patches are welcome.



Also, whilst tracing code through to pqsecure_write(), I also wondered if
some Nagle's algorithm on the socket is also introducing an additional
delay? I can't see special consideration in the code for this (eg.
TCP_NODELAY)


We do set TCP_NODELAY, see connectNoDelay() in fe-connect.c 
(http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/libpq/fe-connect.c;h=ed9dce941e1d57cce51f2c21bf29769dfe2ee542;hb=HEAD#l960)


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Marti Raudsepp
I have nothing of substance to add, but

On Mon, Oct 31, 2011 at 17:44, Mark Hills mark.hi...@framestore.com wrote:
 Instead, it would be preferable to send multiple requests (down the TCP
 socket), and then receive multiple responses (in order).

HTTP calls this pipelining. I think it's helpful to adopt this term
since the concept is already familiar to many developers.

Regards,
Marti

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 31.10.2011 17:44, Mark Hills wrote:
 Could libpq be reasonably modified to allow this?

 I believe it's doable in theory, no-one has just gotten around to it. 
 Patches are welcome.

Can't you do that today with a multi-command string submitted to
PQsendQuery, followed by multiple calls to PQgetResult?

I'm hesitant to think about supporting the case more thoroughly than
that, or with any different semantics than that, because I think that
the error-case behavior will be entirely unintelligible/unmaintainable
unless you abandon all queries-in-flight in toto when an error happens.
Furthermore, in most apps it'd be a serious PITA to keep track of which
reply is for which query, so I doubt that such a feature is of general
usefulness.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Heikki Linnakangas

On 31.10.2011 19:09, Tom Lane wrote:

Heikki Linnakangasheikki.linnakan...@enterprisedb.com  writes:

On 31.10.2011 17:44, Mark Hills wrote:

Could libpq be reasonably modified to allow this?



I believe it's doable in theory, no-one has just gotten around to it.
Patches are welcome.


Can't you do that today with a multi-command string submitted to
PQsendQuery, followed by multiple calls to PQgetResult?


Yes, true, although that only works with the simple query protocol. The 
extended protocol doesn't allow multi-command queries.



I'm hesitant to think about supporting the case more thoroughly than
that, or with any different semantics than that, because I think that
the error-case behavior will be entirely unintelligible/unmaintainable
unless you abandon all queries-in-flight in toto when an error happens.


Abandoning all in-flight queries seems quite reasonable to me. You could 
send a Sync message between each query to make it easier to track which 
query errored.



Furthermore, in most apps it'd be a serious PITA to keep track of which
reply is for which query, so I doubt that such a feature is of general
usefulness.


I think a common use for this would be doing multiple inserts or updates 
on one go. Like, insert into a parent table, then more details into 
child tables. You don't care about getting the results back in that 
case, as long as you get an error on failure.


Another typical use case would be something like an ORM that wants to 
fetch a row from one table, and details of the same object from other 
tables. If it's just 2-3 queries, it's not that difficult to remember in 
which order they were issued.


Both of those use cases would be happy with just sending a multi-command 
string with PQsendQuery(), because you know the all queries in advance, 
but it would be nice to not be limited to simple query protocol...


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Merlin Moncure
On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 31.10.2011 17:44, Mark Hills wrote:
 Could libpq be reasonably modified to allow this?

 I believe it's doable in theory, no-one has just gotten around to it.
 Patches are welcome.

 Can't you do that today with a multi-command string submitted to
 PQsendQuery, followed by multiple calls to PQgetResult?

Multi command string queries don't support parameterization.  The way
I do it is to keep an application managed stack of data (as an array
of record types) to send that is accumulated when the last stack is in
transit.  Then when the last response comes in you repeat.

Of course, if you could parameterize a multi command string statement,
that might be a better way to go.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Mark Hills
On Mon, 31 Oct 2011, Tom Lane wrote:

 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
  On 31.10.2011 17:44, Mark Hills wrote:
  Could libpq be reasonably modified to allow this?
 
  I believe it's doable in theory, no-one has just gotten around to it. 
  Patches are welcome.
 
 Can't you do that today with a multi-command string submitted to
 PQsendQuery, followed by multiple calls to PQgetResult?

I remember something about this; I think I concluded that it validated 
that receiving multiple results could be done this way.

But this kind of batching can't be used with prepared queries.
 
 I'm hesitant to think about supporting the case more thoroughly than 
 that, or with any different semantics than that, because I think that 
 the error-case behavior will be entirely unintelligible/unmaintainable 
 unless you abandon all queries-in-flight in toto when an error happens.

Can you explain a bit more detail which errors are of most concern, do you 
mean full buffers on the client send?

Because the content of the stream going to/from the server does not 
change, I wouldn't really expect the semantics to change. For example, the 
server cannot even see that the client is behaving in this way. Are there 
any 'send' functions that are heavily reliant on some kind of 
result/receive state?

I don't disagree with the comments above though, any shift towards 
unintelligible behaviour would be very bad.

 Furthermore, in most apps it'd be a serious PITA to keep track of which 
 reply is for which query, so I doubt that such a feature is of general 
 usefulness.

In our UI case, we already have a queue. Because libpq can't pipeline 
multiple queries, we have to make our own queue of them anyway.

-- 
Mark

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Merlin Moncure
On Mon, Oct 31, 2011 at 12:49 PM, Mark Hills mark.hi...@framestore.com wrote:
 On Mon, 31 Oct 2011, Tom Lane wrote:

 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
  On 31.10.2011 17:44, Mark Hills wrote:
  Could libpq be reasonably modified to allow this?

  I believe it's doable in theory, no-one has just gotten around to it.
  Patches are welcome.

 Can't you do that today with a multi-command string submitted to
 PQsendQuery, followed by multiple calls to PQgetResult?

 I remember something about this; I think I concluded that it validated
 that receiving multiple results could be done this way.

 But this kind of batching can't be used with prepared queries.

 I'm hesitant to think about supporting the case more thoroughly than
 that, or with any different semantics than that, because I think that
 the error-case behavior will be entirely unintelligible/unmaintainable
 unless you abandon all queries-in-flight in toto when an error happens.

 Can you explain a bit more detail which errors are of most concern, do you
 mean full buffers on the client send?

 Because the content of the stream going to/from the server does not
 change, I wouldn't really expect the semantics to change. For example, the
 server cannot even see that the client is behaving in this way. Are there
 any 'send' functions that are heavily reliant on some kind of
 result/receive state?

 I don't disagree with the comments above though, any shift towards
 unintelligible behaviour would be very bad.

 Furthermore, in most apps it'd be a serious PITA to keep track of which
 reply is for which query, so I doubt that such a feature is of general
 usefulness.

 In our UI case, we already have a queue. Because libpq can't pipeline
 multiple queries, we have to make our own queue of them anyway.

Note, nothing is keeping you from opening up a second connection and
interleaving in that fashion, so 'libpq' is not the bottleneck, the
connection object is :-).

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Merlin Moncure
On Mon, Oct 31, 2011 at 12:49 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 31.10.2011 17:44, Mark Hills wrote:
 Could libpq be reasonably modified to allow this?

 I believe it's doable in theory, no-one has just gotten around to it.
 Patches are welcome.

 Can't you do that today with a multi-command string submitted to
 PQsendQuery, followed by multiple calls to PQgetResult?

 Multi command string queries don't support parameterization.  The way
 I do it is to keep an application managed stack of data (as an array
 of record types) to send that is accumulated when the last stack is in
 transit.  Then when the last response comes in you repeat.

(offlist) in more detail, what I do here is to place action data into
a composite type and parameterize it into an array.  That array is
passed directly to a receiving query or a function if what's happening
in the server is complex.  We wrote a library for that purpose: see
here:

http://libpqtypes.esilo.com/
and especially here:
http://libpqtypes.esilo.com/man3/pqt-composites.html

so that while the connection is busy, and data is coming in from the
app, you continually PQputf() more records into the array that is
going to be shipped off to the server when the connection becomes
available.

On the query that gets to the server, it can be as simple as:
insert into foo select unnest(%foo[])

select work_on_data(%foo[])

libpqtypes sends all the data in native binary formats so is very fast.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Merlin Moncure
On Mon, Oct 31, 2011 at 1:08 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Mon, Oct 31, 2011 at 12:49 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 31.10.2011 17:44, Mark Hills wrote:
 Could libpq be reasonably modified to allow this?

 I believe it's doable in theory, no-one has just gotten around to it.
 Patches are welcome.

 Can't you do that today with a multi-command string submitted to
 PQsendQuery, followed by multiple calls to PQgetResult?

 Multi command string queries don't support parameterization.  The way
 I do it is to keep an application managed stack of data (as an array
 of record types) to send that is accumulated when the last stack is in
 transit.  Then when the last response comes in you repeat.

 (offlist) in more detail, what I do here is to place action data into
 a composite type and parameterize it into an array.  That array is
 passed directly to a receiving query or a function if what's happening
 in the server is complex.  We wrote a library for that purpose: see
 here:

 http://libpqtypes.esilo.com/
 and especially here:
 http://libpqtypes.esilo.com/man3/pqt-composites.html

 so that while the connection is busy, and data is coming in from the
 app, you continually PQputf() more records into the array that is
 going to be shipped off to the server when the connection becomes
 available.

 On the query that gets to the server, it can be as simple as:
 insert into foo select unnest(%foo[])

 select work_on_data(%foo[])

 libpqtypes sends all the data in native binary formats so is very fast.

heh, sorry for the noise here :-).

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Dimitri Fontaine
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 I think a common use for this would be doing multiple inserts or updates on
 one go. Like, insert into a parent table, then more details into child
 tables. You don't care about getting the results back in that case, as long
 as you get an error on failure.

As of 9.1 you can use WITH to achieve that in many cases.
wCTE and INSERT|UPDATE|DELETE … RETURNING are pretty cool combined :)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Multiple queries in transit

2011-10-31 Thread Jeroen Vermeulen

On 2011-11-01 00:53, Merlin Moncure wrote:

On Mon, Oct 31, 2011 at 12:49 PM, Mark Hillsmark.hi...@framestore.com  wrote:



Furthermore, in most apps it'd be a serious PITA to keep track of which
reply is for which query, so I doubt that such a feature is of general
usefulness.


In our UI case, we already have a queue. Because libpq can't pipeline
multiple queries, we have to make our own queue of them anyway.


In libpqxx (the C++ API) you do get support for this kind of pipelining. 
 Look for the pipeline class.  It uses the concatenate queries, 
retrieve multiple results trick.


The pipeline also serves as an easy-to-manage interface for asynchronous 
querying: fire off your query, go do other things while the server is 
working, then ask for the result (at which point you'll block if necessary).


Front page: http://pqxx.org/development/libpqxx/

Pipeline class: 
http://pqxx.org/devprojects/libpqxx/doc/stable/html/Reference/a00062.html


Jeroen

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers