The following bug has been logged online:
Bug reference: 5804
Logged by: Paul J. Davis
Email address: paul.joseph.davis@gmail.com
PostgreSQL version: 9.0.2
Operating system: OS X 10.6.5, Ubuntu 10.04
Description: Connection aborted after many queries.
Details:
After running many queries (millions) a connection will report an error that
the server has unexpectedly closed the connection. I first noticed this
through psycopg2, but I've been able to reproduce it with a small C program
using only libpq which I've included below. I compiled this against a libpq
built by Homebrew (after upgrading the formula to use a 9.0.2 tarball) on OS
X 10.6.5. The server was installed from 9.0.2 package available from
https://launchpad.net/~pitti/+archive/postgresql
My next step is to try building libpq with --enable-cassert to see if that
triggers anything client side. Let me know if there's something else I
should be doing to debug this.
This test has been bailing between 2.6 and 2.7M queries:
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
static void
fail(PGconn* conn, PGresult* res)
{
if(res != NULL) PQclear(res);
PQfinish(conn);
exit(1);
}
static void
check(PGconn* conn, PGresult* res, const char* fmt)
{
ExecStatusType status = PQresultStatus(res);
if(status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)
{
fprintf(stderr, fmt, PQerrorMessage(conn));
fail(conn, res);
}
}
void
run_query(PGconn* conn, PGresult* res)
{
int nFields, i, j;
res = PQexec(conn, "DECLARE myportal CURSOR FOR select 1");
check(conn, res, "DECLARE CURSOR failed: %s");
PQclear(res);
res = PQexec(conn, "FETCH ALL in myportal");
check(conn, res, "FETCH ALL failed: %s");
nFields = PQnfields(res);
for(i = 0; i < PQntuples(res); i++)
{
for(j = 0; j < nFields; j++)
{
PQgetvalue(res, i, j);
}
}
PQclear(res);
res = PQexec(conn, "CLOSE myportal");
check(conn, res, "CLOSE failed: %s");
PQclear(res);
}
int
main(int argc, char **argv)
{
PGconn* conn;
PGresult* res;
int i;
if(argc != 2)
{
fprintf(stderr, "usage: %s DSN\n", argv[0]);
exit(1);
}
conn = PQconnectdb(argv[1]);
if(PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection failed: %s", PQerrorMessage(conn));
fail(conn, NULL);
}
res = PQexec(conn, "BEGIN");
check(conn, res, "BEGIN failed: %s");
PQclear(res);
for(i = 0; i < 10000000; i++)
{
if((i+1) % 100000 == 0)
{
fprintf(stderr, "I: %d\n", i);
}
run_query(conn, res);
}
res = PQexec(conn, "END");
check(conn, res, "END failed: %s");
PQclear(res);
PQfinish(conn);
return 0;
}