Re: stored procedures vs pg_stat_statements

On Tue, Dec 24, 2024 at 08:23:57AM -0600, Merlin Moncure wrote:
> Actually, I hadn't gotten that far yet; I was just noticing that:
> CALL foo(1,2,3);
> CALL foo(2,3,4);
> ...resolved to different queryids and if that was expected, and if not, if
> some logic tune-ups in the extension improve behavior without deeper
> changes.

Oh, yeah, definitely. This has been a severe issue for some customers
I'm dealing with that have a heavy workload based on CALL who also
wish to track utilities on top of non-utility queries. This level of
normalization is supported since 17~ and commit 11c34b342bd7, so you
would need to upgrade if you want that support natively as this
requires in-core backend adjustments:
CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
DECLARE
r int;
BEGIN
SELECT (i+j)::int INTO r;
END; $$ LANGUAGE plpgsql;
CALL sum_two(1,1);
CALL sum_two(2,1000);
CALL sum_two(3,3);
SELECT calls, query FROM pg_stat_statements WHERE query ~ 'CALL';
calls | query
-------+---------------------
3 | CALL sum_two($1,$2)
(1 row)

One piece of facility that was lacking is support for query jumbling
with the parsing nodes of utilities, which was larger than this
specific commit, still it allowed the change. If you see other areas
that could make pgss more useful with more utility normalization, feel
free to point them out. The point would be then to figure out which
portions of the parsing nodes need to be ignored in the query
jumbling and the constant locations.

contrib/pg_stat_statements/sql/utility.sql has a test section
dedicated to CALL, FWIW.
--
Michael