Multiple queries in transit

From: Mark Hills <Mark(dot)Hills(at)framestore(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Multiple queries in transit
Date: 2011-10-31 15:44:02
Message-ID: alpine.LFD.2.01.1110311246420.4558@sys880.ldn.framestore.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Looking at the libpq code (fq-exec.c), it seems almost no state needs to
be stored until results are received, and so perhaps this limitation is
unnecessary. The result-accumulation state is reset on sending the query;
it could perhaps be done on receipt. Are there problems with this?

Below is a simple illustration.

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)

Thoughts and suggestions appreciated, many thanks.

--
Mark

#include <stdio.h>
#include <libpq-fe.h>

#define QUEUE 10

void qerror(const char *label, PGconn *db)
{
fprintf(stderr, "%s: %s", label, PQerrorMessage(db));
}

int main(int argc, char *argv[])
{
unsigned int n;
PGconn *db;

db = PQconnectdb("");
if (PQstatus(db) != CONNECTION_OK) {
qerror("PQconnectdb", db);
return -1;
}

/* Send queries. Important: this simple example does not cover
* the case of a full transmit buffer */

for (n = 0; n < QUEUE; n++) {
fprintf(stderr, "Sending query %u...\n", n);

if (PQsendQuery(db, "SELECT random()") != 1) {
qerror("PQsendQuery", db);
return -1;
}
}

/* Receive responses */

for (n = 0; n < QUEUE; n++) {
PGresult *r;

fprintf(stderr, "Receiving response %u...\n", n);

r = PQgetResult(db);
if (r == NULL) {
qerror("PQgetResult", db);
return -1;
}

fprintf(stderr, " Result is %s\n", PQgetvalue(r, 0, 0));
PQclear(r);
}

PQfinish(db);

return 0;
}

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2011-10-31 16:28:24 Re: Multiple queries in transit
Previous Message Tom Lane 2011-10-31 14:22:36 Re: Clarification on item on Todo List