Bug #933: Too many inserts crash server - Mailing list pgsql-bugs

Mark Pether (mutex@redsheriff.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Too many inserts crash server

Long Description
If I create an external C function consisting of a
simple loop that inserts 250000 records into a table
the server process eventually crashes.

Actually the number of inserts appears to be dependant
upon memory consumption by SPI manager. (so your milage
may vary)

The workaround I found involves periodically calling
SPI_finish + SPI_connect to bounce the manager.

I have included code to illustrate the problem and my workaround.

My platform is:
Intel P3
Mandrake 8.2
512mb RAM
Postgres 7.3
plenty of disk free.

Sample Code
postgres initialisation:

create table triple (a int, b int, c int);
create or replace function crashme() returns void as 'pgtest.so' language 'c';
create or replace function dontcrashme() returns void as 'pgtest.so' language 'c';

the code:

#include <string.h>
#include <sys/time.h>
#include <sys/resource.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <time.h>
#include "avl.h"
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "utils/date.h"
#include "utils/datetime.h"

Datum crashme(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(crashme);
Datum crashme(PG_FUNCTION_ARGS)
{
    const char *DEBUG_NAME = "crashme(): ";

    char *query = "insert into triple values (100, 200, 300)";

    if (SPI_connect() == SPI_OK_CONNECT)
    {
        int i;
        int exec_result;

        for (i=0; i<250000; i++)
        {
            if (!(i%16384))
            {
                elog(INFO, "%sstill alive", DEBUG_NAME);
            }

            if ((exec_result = SPI_exec(query, 0)) != SPI_OK_INSERT)
            {
                elog(ERROR, "%sinsert failed (%d)", DEBUG_NAME, exec_result);
                break;
            }
        }

        SPI_finish();
    }
    else
    {
        elog(ERROR, "%scould not connect", DEBUG_NAME);
    }

    PG_RETURN_NULL();
}

Datum dontcrashme(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(dontcrashme);
Datum dontcrashme(PG_FUNCTION_ARGS)
{
    const char *DEBUG_NAME = "dontcrashme(): ";

    char *query = "insert into triple values (100, 200, 300)";

    if (SPI_connect() == SPI_OK_CONNECT)
    {
        int i;
        int exec_result;

        for (i=0; i<250000; i++)
        {
            if (!(i%16384))
            {
                elog(INFO, "%sbouncing SPI manager", DEBUG_NAME);
                elog(INFO, "%sSPI_finish (%s)",
                        DEBUG_NAME, (SPI_finish() == SPI_OK_FINISH) ? "good" : "bad");
                elog(INFO, "%sSPI_connect (%s)",
                        DEBUG_NAME, (SPI_connect() == SPI_OK_CONNECT) ? "good" : "bad");
            }

            if ((exec_result = SPI_exec(query, 0)) != SPI_OK_INSERT)
            {
                elog(ERROR, "%sinsert failed (%d)", DEBUG_NAME, exec_result);
                break;
            }
        }

        SPI_finish();
    }
    else
    {
        elog(ERROR, "%scould not connect", DEBUG_NAME);
    }

    PG_RETURN_NULL();
}

No file was uploaded with this report