Обсуждение: pgtcl combined with view with certain insert/update-rules => crash

Hi,

since 7.3 we observe repeatable crashes of the backend under certain
conditions.

If pgtcl-function executes an INSERT-command via spi-exec on a view with
an INSERT-rule which actually does not do an insert but a SELECT instead
the backend crashes when the rule-execution finished.

This does not happen if you do the same INSERT via psql-tool or from
within a pgsql-function via EXECUTE.

The same is true for UPDATE-command/rule.

Executing an INSERT as last statement of a rules fixes the problem.

postgresql 7.2 works fine.


Example:

CREATE VIEW a AS
    SELECT f FROM b;

CREATE RULE r AS ON INSERT TO a DO INSTEAD (
    SELECT xyz(NEW.f);
);

xyz is any stored procedure (i.e. written in pgsql).

(though I don't think it is important that a stored procedured is called
at all via the select).


CREATE FUNCTION t () RETURNS VOID AS '
    spi_exec "INSERT INTO a (f) VALUES (''5'')"
' LANGUAGE 'pltcl';


Then calling t from psql i.e. leads to a crash.


If you change the rule r to

CREATE RULE r AS ON INSERT TO a DO INSTEAD (
    SELECT xyz(NEW.f);
    INSERT INTO dummy (bla) VALUES ('abcdefg');
);

(and table dummy exists of course)

then t works fine.




--
Wolfgang Walter
Studentenwerk München
Anstalt des öffentlichen Rechts
EDV
Leopoldstraße 15
80802 München
Tel: +49 89 38196 276
Fax: +49 89 38196 144
wolfgang.walter@studentenwerk.mhn.de
http://www.studentenwerk.mhn.de/

Wolfgang Walter <wolfgang.walter@studentenwerk.mhn.de> writes:
> since 7.3 we observe repeatable crashes of the backend under certain
> conditions.

I can't reproduce your problem... using 7.3 branch here, I did this:

bug=# create table b (f int);
CREATE TABLE
bug=# CREATE VIEW a AS SELECT f FROM b;
CREATE VIEW
bug=# create function xyz(int) returns int as '
bug'# begin
bug'# return $1;
bug'# end' language plpgsql;
CREATE FUNCTION
bug=# select xyz(33);
 xyz
-----
  33
(1 row)

bug=# CREATE RULE r AS ON INSERT TO a DO INSTEAD (
bug(# SELECT xyz(NEW.f);
bug(# );
CREATE RULE
bug=# CREATE FUNCTION t () RETURNS VOID AS '
bug'# spi_exec "INSERT INTO a (f) VALUES (''5'')"
bug'# ' LANGUAGE 'pltcl';
CREATE FUNCTION
bug=# select t();
 t
---

(1 row)

bug=#

Perhaps there is a platform-specific issue, but one thing I'd advise
checking first is whether you are linking to the 7.3 versions of
pltcl.so and plpgsql.so.  If you've got absolute paths in the CREATE
FUNCTION commands for the language handlers, you might be pulling in
old versions of those handlers, leading to all kinds of weird behavior.
Preferably the pg_proc entries should look like

bug=# select proname,prosrc,probin from pg_proc where proname like '%_handler';

       proname        |        prosrc        |     probin
----------------------+----------------------+-----------------
 pltcl_call_handler   | pltcl_call_handler   | $libdir/pltcl
 plpgsql_call_handler | plpgsql_call_handler | $libdir/plpgsql
(2 rows)

            regards, tom lane