Multiple queries in transit - Mailing list 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;
}