Thread: Should PQconsumeInput/PQisBusy be expensive to use?

On Wed, Oct 27, 2010 at 15:02, Michael Clark <codingninja@gmail.com> wrote:
> Hello everyone.
> Upon some investigation I found that not calling PQconsumeInput/PQisBusy
> produces results in line with PQexecParams (which PQexecParams seems to be
> doing under the hood).

> (please keep in mind this is just test code and rather simplistic...)
>     int send_result = PQsendQueryParams(self.db,
>                                         [sql UTF8String],
>                                         i,
>                                         NULL,
>                                         (const char *const *)vals,
>                                         (const int *)lens,
>                                         (const int *)formats,
>                                         kTextResultFormat);
>     int consume_result = 0;
>     int is_busy_result = 0;
>
>     while ( ((consume_result = PQconsumeInput(self.db)) == 1) &&
> ((is_busy_result = PQisBusy(self.db)) == 1) )
>         ;

You really want to select() or equivalent here...  This basically is a
busy loop using 100% cpu; neither PQconsumeInput or PQisBusy do any
kind of sleeping...

Something like:
fd_set read_mask;
int sock = PQsocket(st->con);
FD_ZERO(&read_mask);
FD_SET(sock, &read_mask);

while(1)
{
  struct timeval tv = {5, 0};
  select(sock+1, &read_mask, NULL, NULL, &tv);
  PQconsumeInput(self.db)
  if(!PQisBusy(self.db))
    break;
}

or something...

Michael Clark <codingninja@gmail.com> writes:
> In doing some experiments I found that using
> PQsendQueryParams/PQconsumeInput/PQisBusy/PQgetResult produces slower
> results than simply calling PQexecParams.

Well, PQconsumeInput involves at least one extra kernel call (to see
whether data is available) so I don't know why this surprises you.
The value of those functions is if your application can do something
else useful while it's waiting.  If the data comes back so fast that
you can't afford any extra cycles expended on the client side, then
you don't have any use for those functions.

However, if you do have something useful to do, the problem with
this example code is that it's not doing that, it's just spinning:

>     while ( ((consume_result = PQconsumeInput(self.db)) == 1) &&
> ((is_busy_result = PQisBusy(self.db)) == 1) )
>         ;

That's a busy-wait loop, so it's no wonder you're eating cycles there.
You want to sleep, or more likely do something else productive, when
there is no data available from the underlying socket.  Usually the
idea is to include libpq's socket in the set of files being watched
by select() or poll(), and dispatch off to something that absorbs
the data whenever you see some data is available to read.

            regards, tom lane

On Oct 28, 2010, at 11:08 AM, Michael Clark wrote:

> Hello all.
>
> Thanks a lot for the responses, they are appreciated.
>
> I think I now understand the folly of my loop, and how that was negatively
> impacting my "test".
>
> I tried the suggestion Alex and Tom made to change my loop with a select()
> and my results are now very close to the non-async version.
>
> The main reason for looking at this API is not to support async in our
> applications, that is being achieved architecturally in a PG agnostic way.
> It is to give our PG agnostic layer the ability to cancel queries.
> (Admittedly the queries I mention in these emails are not candidates for
> cancelling...).

Hm- I'm not sure how the async API will allow you to cancel queries. In PostgreSQL, query canceling is implemented by
openinga second connection and passing specific data which is received from the first connection (effectively sending a
cancelsignal to the connection instead of a specific query). This implementation is necessitated by the fact that the
PostgreSQLbackend isn't asynchronous. 

Even if you cancel the query, you still need to consume the socket input. Query cancellation is available for libpq
bothin sync and async modes. 

Cheers,
M

    A.M. wrote:

> In PostgreSQL, query canceling is implemented by opening a
> second connection and passing specific data which is received
> from the first connection

With libpq's PQCancel(), a second connection is not necessary.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

    Michael Clark wrote:

> I guess I can have one thread performing the query using the non async PG
> calls, then from another thread issue the cancellation.  Both threads
> accessing the same PGconn ?

Yes. See http://www.postgresql.org/docs/9.0/static/libpq-cancel.html

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On Oct 28, 2010, at 12:04 PM, Daniel Verite wrote:

>     A.M. wrote:
>
>> In PostgreSQL, query canceling is implemented by opening a
>> second connection and passing specific data which is received
>> from the first connection
>
> With libpq's PQCancel(), a second connection is not necessary.

To clarify, PQcancel() opens a new socket to the backend and sends the cancel message. (The server's socket address is
passedas part of the cancel structure to PQcancel.) 


http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/interfaces/libpq/fe-connect.c;h=8f318a1a8cc5bf2d49b2605dd76581609cf9be32;hb=HEAD#l2964

The point is that a query can be cancelled from anywhere really and cancellation will not use the original connection
socket.

Cheers,
M