From: | "Drouvot, Bertrand" <bdrouvot(at)amazon(dot)com> |
---|---|
To: | Jeremy Schneider <schnjere(at)amazon(dot)com>, Andres Freund <andres(at)anarazel(dot)de> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> |
Subject: | Re: [PATCH] Query Jumbling for CALL and SET utility statements |
Date: | 2022-09-01 10:55:11 |
Message-ID: | cbd28368-a5ae-f141-4c3f-28ba9f608ba0@amazon.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 8/31/22 10:05 PM, Jeremy Schneider wrote:
> 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
Attachment | Content-Type | Size |
---|---|---|
v2-0001-JumbleQuery-on-Call-and-Set.patch | text/plain | 15.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2022-09-01 11:18:17 | Re: pg15b3: recovery fails with wal prefetch enabled |
Previous Message | Amit Kapila | 2022-09-01 09:53:30 | Re: Column Filtering in Logical Replication |