Core dump in PL/pgSQL ...

one of our customers here found a bug in PL/pgSQL.
this is how you can create this one:

CREATE OR REPLACE FUNCTION "public"."make_victim_history" () RETURNS
trigger AS $body$ DECLARE

schemarec RECORD;
exec_schemaselect text;
curs2 refcursor;

BEGIN

exec_schemaselect := 'SELECT nspname FROM pg_class c JOIN
pg_namespace n ON n.oid = c.relnamespace WHERE c.oid = ' || TG_RELID;

OPEN curs2 FOR EXECUTE exec_schemaselect;
FETCH curs2 INTO schemarec;
CLOSE curs2;

RAISE NOTICE 'schemarecord: %',schemarec.nspname;

RAISE NOTICE 'begin new block';
BEGIN
RAISE NOTICE 'insert now';
EXECUTE 'insert into public_history.victim SELECT * from
public.victim where id=1;';

EXCEPTION
WHEN OTHERS THEN
-- do nothing
END;

RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

--TABLE ERSTELLEN
CREATE TABLE "public"."victim" (
"id" BIGINT,
"name" TEXT,
"created" TIMESTAMP WITHOUT TIME ZONE,
"create_user" BIGINT,
"changed" TIMESTAMP WITHOUT TIME ZONE,
"change_user" BIGINT,
"state" SMALLINT
) WITHOUT OIDS;

INSERT INTO victim VALUES (1, 'hans', now(), 2, now(), 3, 4);

-- TRIGGER ERSTELLEN
CREATE TRIGGER "victim_tr" BEFORE UPDATE OR DELETE ON
"public"."victim" FOR EACH ROW EXECUTE PROCEDURE
"public"."make_victim_history"();

-- BAD BAD STATEMENT
UPDATE public.victim SET changed=NOW(), change_user = 1;

a quick fix is to prevent the language from freeing the tuple twice -
this should safely prevent the core dump here.
we still have to make sure that the tuple if freed properly. stay tuned.
here is the patch ...

hans

diff -rc postgresql-8.2.0-orig/src/backend/executor/spi.c
postgresql-8.2.0/src/backend/executor/spi.c
*** postgresql-8.2.0-orig/src/backend/executor/spi.c Tue Nov 21
23:35:29 2006
--- postgresql-8.2.0/src/backend/executor/spi.c Tue Dec 19 15:04:42 2006
***************
*** 264,270 ****
/* free Executor memory the same as _SPI_end_call would do */
MemoryContextResetAndDeleteChildren(_SPI_current->execCxt);
/* throw away any partially created tuple-table */
! SPI_freetuptable(_SPI_current->tuptable);
_SPI_current->tuptable = NULL;
}
}
--- 264,270 ----
/* free Executor memory the same as _SPI_end_call would do */
MemoryContextResetAndDeleteChildren(_SPI_current->execCxt);
/* throw away any partially created tuple-table */
! // SPI_freetuptable(_SPI_current->tuptable);
_SPI_current->tuptable = NULL;
}
}

--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at