PQreset needs to be executed twice to really connect to the postgresql database

From: ChoonSoo Park <luispark(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PQreset needs to be executed twice to really connect to the postgresql database
Date: 2011-12-16 22:31:55
Message-ID: CACgbiFvYzUxu3KHLzzoi25m9nPAcnk_WToHA7TZPJYkaVAzOvA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Here is my sample code and I had to execute PQreset twice to connect to the
database after asynchronous command ( PQsendQueryParams ).

#include <libpq-fe.h>
#include <iostream>
#include <stdlib.h>

using namespace std;

bool checkStatus(PGconn* conn, bool readable, bool writable, int second,
int
microsecond)
{
int sock = PQsocket(conn);
fd_set read_mask, write_mask;
struct timeval timer;

if (sock > 0) {
timer.tv_sec = second;
timer.tv_usec = microsecond;
FD_ZERO(&read_mask);
FD_ZERO(&write_mask);
if (readable) {
FD_SET(sock, &read_mask);
}
if (writable) {
FD_SET(sock, &write_mask);
}

if (select(sock + 1, (readable) ? &read_mask: NULL, (writable) ?
&write_mask : NULL, NULL, &timer) == 0) {
} else if ( readable && FD_ISSET(sock, &read_mask)) {
return true;
} else if ( writable && FD_ISSET(sock, &write_mask)) {
return true;
}
}
return false;
}

main(int argc, char* argv[])
{
bool success;
PGconn* conn;

const char* connInfo = argv[1];

conn = PQconnectdb(connInfo);
ConnStatusType type = PQstatus(conn);
if (type == CONNECTION_BAD) {
cout << PQerrorMessage(conn) << endl;
exit(1);
} else {
cout << "connected" << endl;
}

Retry:
const char* sql = "select * from pg_sleep(20)";
int nResult = PQsendQueryParams(conn,
sql, 0, NULL, NULL, NULL, NULL, 0);
cout << "Sent sleepquery...." << endl;
if (nResult == 1) {
PQflush(conn);
while (PQisBusy(conn) == 1) {
success = checkStatus(conn, true, false, 1, 0);
if (success) {
PQconsumeInput(conn);
} else {
if (PQstatus(conn) == CONNECTION_BAD) {
PQreset(conn);
if (PQstatus(conn) == CONNECTION_BAD) {
cout << "still invalid connection after PQreset" <<
endl;
sleep(1);
PQreset(conn);
}
goto Retry;
} else {
cout << ".";
cout.flush();
}
}
}

PGresult* rst = NULL;
while ((rst = PQgetResult(conn)) != NULL) {
int r, nRows = PQntuples(rst);
int c, nColumns = PQnfields(rst);
for (r = 0; r < nRows; r++) {
for (c = 0; c < nColumns; c++) {
cout << PQgetvalue(rst, r, c) << " ";
}
cout << endl;
}
PQclear(rst);
}
} else {
cout << PQerrorMessage(conn) << endl;
}
}

Once you run this program, you will see "." being printed on the console.
If you intentionally kill the postgres process instance, you will see the
following output.

connected
Sent sleepquery....
........still invalid connection after PQreset
Sent sleepquery....
...................

You will see "still invalid connection after PQreset".

For synchronous command (PQExec), PQreset works fine. However for
asynchronous command like PQsendQueryParams, PQreset doesn't seem to work.
I had to sleep a few seconds and then run PQreset again.

Anyone running into this problem?

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Kevin McCaffrey 2011-12-17 01:46:09 New User: PostgreSQL Setup - "The Program 'postgress' is needed by initdb but was not found in the same directory..."
Previous Message Mike Blackwell 2011-12-16 20:22:53 disable autovacuum per-database?