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
test_plpgsql.patch
Description: Binary data
