BUG #5804: Connection aborted after many queries. - Mailing list pgsql-bugs

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;
}