Обсуждение: Bug #933: Too many inserts crash server

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

pgsql-bugs@postgresql.org writes:
> If I create an external C function consisting of a
> simple loop that inserts 250000 records into a table
> the server process eventually crashes.

The memory leak goes away if you add

    SPI_freetuptable(SPI_tuptable);

after the SPI_exec() call.

I am not sure whether to consider this an SPI bug or not.  It's possibly
surprising that a non-SELECT query generates an empty tuple table; but
on the other hand, it's hard to say that no non-SELECT query can ever
generate a tuple table.  (EXPLAIN is a counterexample, for instance.)

It may be better to document that you should always do
SPI_freetuptable() no matter what kind of query you executed.

            regards, tom lane

Ok,

Thanks for advice I'll add SPI_freetuple() to my program.

I must say I had a difficult time trying to work around this problem
(couldn't find any docs),
so you may want to add note into Postgres 7.3 Server Programming docs.

By the way, my workaround successfully used was:

 SPI_push();
 SPI_connect();

do loop
    if loop % 16834 == 0
       SPI_finish()
       SPI_connect()
    end if
end loop;

SPI_finish()
SPI_pop();

This also freed memory, but doesn't look nice and uses the undoc'd push
and pop calls.

Regards,
Mark Pether.



Tom Lane wrote:

>pgsql-bugs@postgresql.org writes:
>
>
>>If I create an external C function consisting of a
>>simple loop that inserts 250000 records into a table
>>the server process eventually crashes.
>>
>>
>
>The memory leak goes away if you add
>
>    SPI_freetuptable(SPI_tuptable);
>
>after the SPI_exec() call.
>
>I am not sure whether to consider this an SPI bug or not.  It's possibly
>surprising that a non-SELECT query generates an empty tuple table; but
>on the other hand, it's hard to say that no non-SELECT query can ever
>generate a tuple table.  (EXPLAIN is a counterexample, for instance.)
>
>It may be better to document that you should always do
>SPI_freetuptable() no matter what kind of query you executed.
>
>            regards, tom lane
>
>
>
>

I meant SPI_freetuptable() by the way  :-)

Regards,
Mark.