Re: Odd behavior with pg_stat_statements and queries called from SQL functions

Hi,

On Wed, Nov 16, 2022 at 11:26:09PM -0800, Maciek Sakrejda wrote:
> I noticed an odd behavior today in pg_stat_statements query
> normalization for queries called from SQL-language functions. If I
> have three functions that call an essentially identical query (the
> functions are only marked SECURITY DEFINER to prevent inlining):
>
> maciek=# create or replace function f1(f1param text) returns text
> language sql as 'select f1param' security definer;
> CREATE FUNCTION
> maciek=# create or replace function f2(f2param text) returns text
> language sql as 'select f2param' security definer;
> CREATE FUNCTION
> maciek=# create or replace function f3(text) returns text language sql
> as 'select $1' security definer;
> CREATE FUNCTION
> [...]
> maciek=# select queryid, query, calls from pg_stat_statements where
> queryid = 6741491046520556186;
> queryid | query | calls
> ---------------------+----------------+-------
> 6741491046520556186 | select f1param | 3
> (1 row)
>
> If I call f3 first, then f2 and f1, I get
>
> maciek=# select queryid, query, calls from pg_stat_statements where
> queryid = 6741491046520556186;
> queryid | query | calls
> ---------------------+-----------+-------
> 6741491046520556186 | select $1 | 3
> (1 row)
>
> I understand that the query text may be captured differently for
> different queries that map to the same id, but it seems confusing that
> parameter names referenced in queries called from functions are not
> normalized away, since they're not germane to the query execution
> itself, and the context of the function is otherwise stripped away by
> this point. I would expect that all three of these queries end up in
> pg_stat_statements with the query text "select $1".Thoughts?

None of those queries actually contain any constant, so the query text is just
saved as-is in all the versions.

I'm not sure that doing normalization for parameters would give way better
results. It's true that a parameter name can change between different
functions running the exact same statements, but is it really likely to happen?
And what if the two functions have different number of parameters in different
orders? $1 could mean different things in different cases, and good luck
finding out which one it is. At least with the parameter name you have a
chance to figure out what the parameter was exactly.