Skip site navigation (1) Skip section navigation (2)

BUG #5804: Connection aborted after many queries.

From: "Paul J(dot) Davis" <paul(dot)joseph(dot)davis(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5804: Connection aborted after many queries.
Date: 2010-12-29 15:30:12
Message-ID: 201012291530.oBTFUCd0036612@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5804
Logged by:          Paul J. Davis
Email address:      paul(dot)joseph(dot)davis(at)gmail(dot)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;
}

Responses

pgsql-bugs by date

Next:From: Paul DavisDate: 2010-12-29 15:54:46
Subject: Re: BUG #5804: Connection aborted after many queries.
Previous:From: yiranwangDate: 2010-12-29 14:54:10
Subject: BUG #5803: Not can backup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group