Odd behavior with pg_stat_statements and queries called from SQL functions

From: Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Odd behavior with pg_stat_statements and queries called from SQL functions
Date: 2022-11-17 07:26:09
Message-ID: CAOtHd0DCke0mun5KfiK30XNGxvJKr425+2Rq69V5Qx+GCnhs=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

and I have pg_stat_statements.track = 'all', so that queries called
from functions are tracked, these all end up with the same query id in
pg_stat_statements, but the query text includes the parameter name (if
one is referenced in the query in the function). E.g., if I call f1
first, then f2 and f3, I get:

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?

Thanks,
Maciek

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2022-11-17 07:28:21 Re: ubsan fails on 32bit builds
Previous Message Bharath Rupireddy 2022-11-17 07:22:46 Re: when the startup process doesn't (logging startup delays)