Thread: consequent PQsendQueryPrepared() failed: another command is already in progress

consequent PQsendQueryPrepared() failed: another command is already in progress

From
Anton Maksimenkov
Date:
I'm using libpq C Library. I prepared some query and trying to call it
many times.
But it success only at first time, and then fail with error:

... "another command is already in progress"

Here is my testbed:
int
main (register int const argc, register char *const argv[])
{
    PGconn        *conn;
    PGresult    *res;

    conn = PQsetdbLogin(PGHOST, PGPORT, PGOPTIONS, PGTTY, PGDBNAME,
PGLOGIN, PGPWD);
    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "PQstatus(): %s", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }
    if ((res = PQprepare(conn, "GET_USER", "SELECT uid FROM users WHERE
uid = $1::INT LIMIT 1", 1, NULL)) == NULL) {
        fprintf(stderr, "PQprepare() res == NULL");
        PQfinish(conn);
        exit(1);
    }
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "PQprepare() failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        exit(1);
    }

    fprintf(stderr, "FIRST: ");
    query(conn);

    fprintf(stderr, "SECOND: ");
    query(conn);

    exit(0);
}

int
query(PGconn *conn)
{
    const char *paramValues[1];
    int            paramLengths[1];
    int            paramFormats[1];
    uint32_t    binaryIntVal;
    PGresult   *res;

    binaryIntVal = htonl((uint32_t) 15);
    paramValues[0] = (char *) &binaryIntVal;
    paramLengths[0] = sizeof(binaryIntVal);
    paramFormats[0] = 1;

    if (PQsendQueryPrepared(conn, "GET_USER", 1, paramValues,
paramLengths, paramFormats, 1) == 0) {
        fprintf(stderr, "PQsendQueryPrepared() failed: %s", PQerrorMessage(conn));
        return -1;
    }
    while (PQisBusy(conn))
        if (PQconsumeInput(conn) == 0) {
            fprintf(stderr, "PQconsumeInput() failed: %s", PQerrorMessage(conn));
            return -1;
        }

    if ((res = PQgetResult(conn)) == NULL) {
        fprintf(stderr, "PQgetResult() res == NULL");
        PQfinish(conn);
        return -1;
    }
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "PQgetResult() failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        return -1;
    }

    int i, uidFN;
    char *uidPTR;
    int uid;

    uidFN = PQfnumber(res, "uid");
    printf("tuples %d\n", PQntuples(res));
    for (i = 0; i < PQntuples(res); i++) {
        uidPTR = PQgetvalue(res, i, uidFN);
        uid = ntohl(*((uint32_t *) uidPTR));
        printf("tuple %d: uid[%d]\n", i, uid);
    }
    PQclear(res);

    return 0;
}

$ ./test
FIRST: tuples 1
tuple 0: uid[15]
SECOND: PQsendQueryPrepared() failed: another command is already in progress

Where I was wrong?


And another question. Is it possible to simultaneously keep a number
of prepared queries and run any of them from time to time?
Something like this:
{
 PQprepare("Q1");
 PQprepare("Q2");
 PQprepare("Q3");
 ...
 query(Q1);
 ...
 query(Q2);
 ...
 query(Q1);
 ...
 query(Q3);
 ...
 query(Q2);
 ...
 query(Q3);
 ...
 query(Q1);
 ...
}
--
antonvm

Anton Maksimenkov <anton200@gmail.com> writes:
> I'm using libpq C Library. I prepared some query and trying to call it
> many times.
> But it success only at first time, and then fail with error:
> ... "another command is already in progress"
> [ PQsendQuery followed by just one PQgetResult ]

IIRC, you need to keep calling PQgetResult until it returns NULL
before the connection is considered cleared and ready for another query.
In this example you should get a NULL on the very next call, but
you didn't make that call.

            regards, tom lane

Anton Maksimenkov wrote:
> I'm using libpq C Library. I prepared some query and trying to call it
> many times.
> But it success only at first time, and then fail with error:
>
> ... "another command is already in progress"
>
You are using the asynchronous queries while the testbed example program
does not have a structure where an asynchronous loop is useful, and
while doing that made an error probably with the consumeinput/isbusy
duo. The problem will probably be gone when you switch to the
synchronous libpq functions (for execute a prepared command)

If you really want the asynchronous api, I found the PQtrace facility
very useful, to debug what's actually going on (or not).

regards,
Yeb Havinga

> Here is my testbed:
> int
> main (register int const argc, register char *const argv[])
> {
>     PGconn        *conn;
>     PGresult    *res;
>
>     conn = PQsetdbLogin(PGHOST, PGPORT, PGOPTIONS, PGTTY, PGDBNAME,
> PGLOGIN, PGPWD);
>     if (PQstatus(conn) == CONNECTION_BAD) {
>         fprintf(stderr, "PQstatus(): %s", PQerrorMessage(conn));
>         PQfinish(conn);
>         exit(1);
>     }
>     if ((res = PQprepare(conn, "GET_USER", "SELECT uid FROM users WHERE
> uid = $1::INT LIMIT 1", 1, NULL)) == NULL) {
>         fprintf(stderr, "PQprepare() res == NULL");
>         PQfinish(conn);
>         exit(1);
>     }
>     if (PQresultStatus(res) != PGRES_COMMAND_OK) {
>         fprintf(stderr, "PQprepare() failed: %s", PQerrorMessage(conn));
>         PQclear(res);
>         PQfinish(conn);
>         exit(1);
>     }
>
>     fprintf(stderr, "FIRST: ");
>     query(conn);
>
>     fprintf(stderr, "SECOND: ");
>     query(conn);
>
>     exit(0);
> }
>
> int
> query(PGconn *conn)
> {
>     const char *paramValues[1];
>     int            paramLengths[1];
>     int            paramFormats[1];
>     uint32_t    binaryIntVal;
>     PGresult   *res;
>
>     binaryIntVal = htonl((uint32_t) 15);
>     paramValues[0] = (char *) &binaryIntVal;
>     paramLengths[0] = sizeof(binaryIntVal);
>     paramFormats[0] = 1;
>
>     if (PQsendQueryPrepared(conn, "GET_USER", 1, paramValues,
> paramLengths, paramFormats, 1) == 0) {
>         fprintf(stderr, "PQsendQueryPrepared() failed: %s", PQerrorMessage(conn));
>         return -1;
>     }
>     while (PQisBusy(conn))
>         if (PQconsumeInput(conn) == 0) {
>             fprintf(stderr, "PQconsumeInput() failed: %s", PQerrorMessage(conn));
>             return -1;
>         }
>
>     if ((res = PQgetResult(conn)) == NULL) {
>         fprintf(stderr, "PQgetResult() res == NULL");
>         PQfinish(conn);
>         return -1;
>     }
>     if (PQresultStatus(res) != PGRES_TUPLES_OK) {
>         fprintf(stderr, "PQgetResult() failed: %s", PQerrorMessage(conn));
>         PQclear(res);
>         PQfinish(conn);
>         return -1;
>     }
>
>     int i, uidFN;
>     char *uidPTR;
>     int uid;
>
>     uidFN = PQfnumber(res, "uid");
>     printf("tuples %d\n", PQntuples(res));
>     for (i = 0; i < PQntuples(res); i++) {
>         uidPTR = PQgetvalue(res, i, uidFN);
>         uid = ntohl(*((uint32_t *) uidPTR));
>         printf("tuple %d: uid[%d]\n", i, uid);
>     }
>     PQclear(res);
>
>     return 0;
> }
>
> $ ./test
> FIRST: tuples 1
> tuple 0: uid[15]
> SECOND: PQsendQueryPrepared() failed: another command is already in progress
>
> Where I was wrong?
>
>
> And another question. Is it possible to simultaneously keep a number
> of prepared queries and run any of them from time to time?
> Something like this:
> {
>  PQprepare("Q1");
>  PQprepare("Q2");
>  PQprepare("Q3");
>  ...
>  query(Q1);
>  ...
>  query(Q2);
>  ...
>  query(Q1);
>  ...
>  query(Q3);
>  ...
>  query(Q2);
>  ...
>  query(Q3);
>  ...
>  query(Q1);
>  ...
> }
>


On Wed, 2010-06-16 at 10:26 +0600, Anton Maksimenkov wrote:
>     if ((res = PQgetResult(conn)) == NULL) {
>         fprintf(stderr, "PQgetResult() res == NULL");
>         PQfinish(conn);
>         return -1;
>     }
>     if (PQresultStatus(res) != PGRES_TUPLES_OK) {
>         fprintf(stderr, "PQgetResult() failed: %s", PQerrorMessage(conn));
>         PQclear(res);
>         PQfinish(conn);
>         return -1;
>     }
>


> SECOND: PQsendQueryPrepared() failed: another command is already in progress
>
> Where I was wrong?
>

You need to call PQgetResult() again. From the docs
http://www.postgresql.org/docs/9.0/static/libpq-async.html :

"PQgetResult must be called repeatedly until it returns a null pointer,
indicating that the command is done."

After you get a NULL back from PQgetResult, you can execute another
command.


>
> And another question. Is it possible to simultaneously keep a number
> of prepared queries and run any of them from time to time?

Yes, although a prepared query lasts only as long as the connection.
When you disconnect and reconnect, you will need to prepare them again.

Regards,
    Jeff Davis


2010/6/16 Tom Lane <tgl@sss.pgh.pa.us>:
>> But it success only at first time, and then fail with error:
>> ... "another command is already in progress"
>> [ PQsendQuery followed by just one PQgetResult ]
>
> IIRC, you need to keep calling PQgetResult until it returns NULL
> before the connection is considered cleared and ready for another query.

Yes, thanks, that was exactly what I missed.

Thanks to all, guys.
--
antonvm