Re: plpgsq_plugin's stmt_end() is not called when an error is caught

Hi,

While investigating the issue reported on pg_hint_plan[1], I realized
that stmt_end() callback is not called if an error raised during the
statement execution is caught. I've attached the patch to check when
stmt_beg() and stmt_end() are called. Here is an example:

postgres(1:3220232)=# create or replace function testfn(a text) returns int as
$$
declare
  x int;
begin
  select a::int into x;
  return x;
  exception when others then return 99;
end;
$$
language plpgsql;
CREATE FUNCTION

postgres(1:3220232)=# select testfn('1');
NOTICE:  stmt_beg toplevel_block
NOTICE:  stmt_beg stmt SQL statement
NOTICE:  stmt_end stmt SQL statement
NOTICE:  stmt_beg stmt RETURN
NOTICE:  stmt_end stmt RETURN
NOTICE:  stmt_end toplevel_block
 testfn
--------
      1
(1 row)

postgres(1:3220232)=# select testfn('x');
NOTICE:  stmt_beg toplevel_block
NOTICE:  stmt_beg stmt SQL statement
NOTICE:  stmt_beg stmt RETURN
NOTICE:  stmt_end stmt RETURN
NOTICE:  stmt_end toplevel_block
 testfn
--------
     99
(1 row)

In exec_stmt_block(), we call exec_stmts() in a PG_TRY() block and
call stmt_beg() and stmt_end() callbacks for each statement executed
there. However, if an error is caught during executing a statement, we
jump to PG_CATCH() block in exec_stmt_block() so we don't call
stmt_end() callback that is supposed to be called in exec_stmts(). To
fix it, I think we can call stmt_end() callback in PG_CATCH() block.

pg_hint_plan increments and decrements a count in stmt_beg() and
stmt_end() callbacks, respectively[2]. It resets the counter when
raising an ERROR (not caught). But if an ERROR is caught, the counter
could be left as an invalid value.

Is this a bug in plpgsql?

Regards,

[1] https://github.com/ossc-db/pg_hint_plan/issues/93
[2] https://github.com/ossc-db/pg_hint_plan/blob/master/pg_hint_plan.c#L4870

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com