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
