Lists: | pgsql-general |
---|
From: | Anton Maksimenkov <anton200(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | consequent PQsendQueryPrepared() failed: another command is already in progress |
Date: | 2010-06-16 04:26:49 |
Message-ID: | AANLkTimXyVLnXSGupMC0UJPuUmN_4EPLgjqpoHe8OlcQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Yeb Havinga <yebhavinga(at)gmail(dot)com> |
---|---|
To: | Anton Maksimenkov <anton200(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: consequent PQsendQueryPrepared() failed: another command is already in progress |
Date: | 2010-06-16 12:58:48 |
Message-ID: | 4C18CA88.3060704@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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);
> ...
> }
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Anton Maksimenkov <anton200(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: consequent PQsendQueryPrepared() failed: another command is already in progress |
Date: | 2010-06-16 14:39:20 |
Message-ID: | 6768.1276699160@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Anton Maksimenkov <anton200(at)gmail(dot)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
From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Anton Maksimenkov <anton200(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: consequent PQsendQueryPrepared() failed: another command is already in progress |
Date: | 2010-06-16 17:43:57 |
Message-ID: | 1276710237.5362.20.camel@jdavis |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Anton Maksimenkov <anton200(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: consequent PQsendQueryPrepared() failed: another command is already in progress |
Date: | 2010-06-17 05:21:11 |
Message-ID: | AANLkTilgPIc8nEY6i3ckoqGrZkqvS7qBlLsBL2LA_9Cr@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2010/6/16 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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