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;

  }

  }