Re: [PATCH] Query Jumbling for CALL and SET utility statements

Hi,

On 8/31/22 12:06 PM, Andres Freund wrote:

Regarding SET, the compelling use case was around "application_name"
whose purpose is to provide a label in pg_stat_activity and on log
lines, which can be used to improve observability and connect queries to
their source in application code.

I wasn't saying that SET shouldn't be jumbled, just that it seems more
reasonable to track it only when track_utility is enabled, rather than doing
so even when that's disabled. Which I do think makes sense for executing a
prepared statement and calling a procedure, since they're really only utility
statements by accident.

I get your point about CALL, maybe it does make sense to also exclude this.

That's a good point and i think we should track CALL whatever the value of pgss_track_utility is.

I think so because we are tracking function calls in all the cases (because "linked" to select aka not a utility) and i don't see any reasons why not to do the same for procedure calls.

Please find attached v2 as an attempt to do so.

With v2 we get things like:

postgres=# set pg_stat_statements.track_utility=on;
SET
postgres=# call MY_PROC(20);
CALL
postgres=# call MY_PROC(10);
CALL
postgres=# set enable_seqscan=false;
SET
postgres=# set enable_seqscan=true;
SET
postgres=# select queryid,query,calls from pg_stat_statements;
       queryid       |                  query                  | calls
---------------------+-----------------------------------------+-------
 4670878543381973400 | set pg_stat_statements.track_utility=$1 |     1
 -640317129591544054 | set enable_seqscan=$1                   |     2
  492647827690744963 | select pg_stat_statements_reset()       |     1
 6541399678435597534 | call MY_PROC($1)                        |     2

and

postgres=# set pg_stat_statements.track_utility=off;
SET
postgres=# call MY_PROC(10);
CALL
postgres=# call MY_PROC(20);
CALL
postgres=# set enable_seqscan=true;
SET
postgres=# set enable_seqscan=false;
SET
postgres=# select queryid,query,calls from pg_stat_statements;
       queryid       |                  query                  | calls
---------------------+-----------------------------------------+-------
 4670878543381973400 | set pg_stat_statements.track_utility=$1 |     1
  492647827690744963 | select pg_stat_statements_reset()       |     1
 6541399678435597534 | call MY_PROC($1)                        |     2
(3 rows)

It might also be worth a small update to the doc for track_utility about how it behaves, in this regard.

https://www.postgresql.org/docs/14/pgstatstatements.html#id-1.11.7.39.9

Example updated sentence:
> pg_stat_statements.track_utility controls whether <<most>> utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE <<, but this parameter does not disable tracking of PREPARE, EXECUTE or CALL>>. The default value is on. Only superusers can change this setting.

Agree, wording added to v2.
Regards,

-- 
Bertrand Drouvot
Amazon Web Services: https://aws.amazon.com