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

čt 15. 12. 2022 v 8:25 odesílatel Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
napsal:

> 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?
>

I think it is by design. There is not any callback that is called after an
exception.

It is true, so some callbacks on statement error and function's error can
be nice. It can help me to implement profilers, or tracers more simply and
more robustly.

But I am not sure about performance impacts. This is on a critical path.

Regards

Pavel

> 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
>