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

backend crash with FATAL: BeginInternalSubTransaction: unexpected state END

From: Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: backend crash with FATAL: BeginInternalSubTransaction: unexpected state END
Date: 2007-05-30 13:49:52
Message-ID: 200705301549.53840.ftm.van.vugt@foxi.nl (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

Migrating a number of sql-functions to plpgsql-functions with added 
functionality resulted in a backend crash.

# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3
(1 row)


The problem is easily reproduced by a copy&paste of the following code in a 
terminal:

********************************************************
create table f1(id int);

CREATE OR REPLACE FUNCTION f1_crash()
RETURNS int
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
SECURITY INVOKER
AS '	DECLARE
		result INT := 0;
	BEGIN
		BEGIN
			SELECT INTO STRICT result 1;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					RAISE EXCEPTION ''Unknown record...!!'';
				WHEN TOO_MANY_ROWS THEN
					RAISE EXCEPTION ''More than one record found...'';
		END;
		RETURN result;
	END;';

CREATE OR REPLACE FUNCTION tr_f1_def()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE
STRICT
SECURITY INVOKER
AS '	DECLARE
	BEGIN
		IF f1_crash() THEN
			RAISE NOTICE ''We got to here...'';
		END IF;
	RETURN NULL;
	END;';

CREATE CONSTRAINT TRIGGER f1_def AFTER INSERT ON f1 DEFERRABLE INITIALLY 
DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f1_def();
********************************************************

After which these statements will run ok:

	insert into f1 values (1);
	insert into f1 select * from generate_series(1, 1000);

However this will fail:

	begin;
	insert into f1 select * from generate_series(1, 1000);
	commit;

Resulting in:

FATAL:  BeginInternalSubTransaction: unexpected state END
CONTEXT:  PL/pgSQL function "f1_crash" line 4 at block variables 
initialization
PL/pgSQL function "tr_f1_def" line 3 at if
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.


The problem seems to be caused by the nested BEGIN/END block in f1_crash(), 
but we need that there in order to separate the RETURN from the EXCEPTION 
block....


Cleanup:
********************************************************
drop table f1 cascade;
drop function tr_f1_def();
drop function f1_crash();
********************************************************


Looking forward to your remarks !



-- 
Best,





Frank.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-05-30 15:04:42
Subject: Re: backend crash with FATAL: BeginInternalSubTransaction: unexpected state END
Previous:From: Andrew SullivanDate: 2007-05-30 11:26:47
Subject: Re: BUG #3321: No start service

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