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

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.

Hey Andres, sorry for misunderstanding your email!

Based on this quick test I just now ran (transcript below), I think that
PREPARE/EXECUTE is already excluded from track_utility?

I get your point about CALL, maybe it does make sense to also exclude
this. 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.

=====

pg-14.4 rw root(at)db1=# set pg_stat_statements.track_utility=on;
SET
pg-14.4 rw root(at)db1=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)
pg-14.4 rw root(at)db1=# prepare test as select /* unique123 */ 1;
PREPARE
pg-14.4 rw root(at)db1=# execute test;
 ?column?
----------
        1
(1 row)

pg-14.4 rw root(at)db1=# set application_name='test';
SET
pg-14.4 rw root(at)db1=# select substr(query,1,50) from pg_stat_statements;
                  substr
-------------------------------------------
 prepare test as select /* unique123 */ $1
 select pg_stat_statements_reset()
 set application_name=$1
(3 rows)

=====

pg-14.4 rw root(at)db1=# set pg_stat_statements.track_utility=off;
SET
pg-14.4 rw root(at)db1=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)

pg-14.4 rw root(at)db1=# prepare test as select /* unique123 */ 1;
PREPARE
pg-14.4 rw root(at)db1=# execute test;
 ?column?
----------
        1
(1 row)

pg-14.4 rw root(at)db1=# set application_name='test';
SET
pg-14.4 rw root(at)db1=# select substr(query,1,50) from pg_stat_statements;
                  substr
-------------------------------------------
 prepare test as select /* unique123 */ $1
 select pg_stat_statements_reset()
(2 rows)

--
Jeremy Schneider
Database Engineer
Amazon Web Services