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

Bug #933: Too many inserts crash server

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #933: Too many inserts crash server
Date: 2003-04-04 02:11:32
Message-ID: 20030404021132.0B316475ED4@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Mark Pether (mutex(at)redsheriff(dot)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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2003-04-04 07:39:47
Subject: Re: Bug #933: Too many inserts crash server
Previous:From: drdaveDate: 2003-04-04 01:33:13
Subject: Re: 'query was cancelled' - depending on search pattern

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