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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
