Subject: Re: [PATCH] Query Jumbling for CALL and SET utility statements
Content-Type: multipart/alternative;
	boundary="------------n8priadbOn7u9PBtn45XZXG0"
Message-ID: <e43a0e02-4ef4-d510-a00a-cf39d2a9df19@amazon.com>
Date: Wed, 31 Aug 2022 13:05:39 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.2.0
Content-Language: en-US
To: Andres Freund <andres@anarazel.de>
CC: "Drouvot, Bertrand" <bdrouvot@amazon.com>, PostgreSQL Hackers
	<pgsql-hackers@lists.postgresql.org>, Peter Eisentraut
	<peter.eisentraut@enterprisedb.com>, Pavel Stehule <pavel.stehule@gmail.com>,
	Nikolay Samokhvalov <samokhvalov@gmail.com>
References: <36e5bffe-e989-194f-85c8-06e7bc88e6f7@amazon.com>
 <20220831160839.wseeotr7rckucpmv@awork3.anarazel.de>
 <46ca04f9-1e37-c862-9a28-c5dffaee412f@amazon.com>
 <20220831190604.l2luuhj3xkau7dvk@awork3.anarazel.de>
From: Jeremy Schneider <schnjere@amazon.com>
In-Reply-To: <20220831190604.l2luuhj3xkau7dvk@awork3.anarazel.de>
Archived-At: 
 <https://www.postgresql.org/message-id/e43a0e02-4ef4-d510-a00a-cf39d2a9df19%40amazon.com>
Precedence: bulk

--------------n8priadbOn7u9PBtn45XZXG0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit

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@db1=# set pg_stat_statements.track_utility=on;
SET
pg-14.4 rw root@db1=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

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

pg-14.4 rw root@db1=# set application_name='test';
SET
pg-14.4 rw root@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@db1=# set pg_stat_statements.track_utility=off;
SET
pg-14.4 rw root@db1=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)

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

pg-14.4 rw root@db1=# set application_name='test';
SET
pg-14.4 rw root@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

--------------n8priadbOn7u9PBtn45XZXG0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 8/31/22 12:06 PM, Andres Freund
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:20220831190604.l2luuhj3xkau7dvk@awork3.anarazel.de">
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">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.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
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.
</pre>
    </blockquote>
    <br>
    Hey Andres, sorry for misunderstanding your email!<br>
    <br>
    Based on this quick test I just now ran (transcript below), I think
    that PREPARE/EXECUTE is already excluded from track_utility?<br>
    <br>
    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.<br>
    <br>
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/14/pgstatstatements.html#id-1.11.7.39.9">https://www.postgresql.org/docs/14/pgstatstatements.html#id-1.11.7.39.9</a><br>
    <br>
    Example updated sentence:<br>
    &gt; <code class="varname">pg_stat_statements.track_utility</code>
    controls whether &lt;&lt;most&gt;&gt; utility commands are tracked
    by the module. Utility commands are all those other than <code
      class="command">SELECT</code>, <code class="command">INSERT</code>,
    <code class="command">UPDATE</code> and <code class="command">DELETE</code>
    &lt;&lt;, but this parameter does not disable tracking of PREPARE,
    EXECUTE or CALL&gt;&gt;. The default value is <code class="literal">on</code>.
    Only superusers can change this setting.<br>
    <br>
    <br>
    <font face="monospace">=====<br>
      <br>
      pg-14.4 rw root@db1=# set pg_stat_statements.track_utility=on;<br>
      SET<br>
      pg-14.4 rw root@db1=# select pg_stat_statements_reset();<br>
       pg_stat_statements_reset<br>
      --------------------------<br>
      <br>
      (1 row)<br>
      pg-14.4 rw root@db1=# prepare test as select /* unique123 */ 1;<br>
      PREPARE<br>
      pg-14.4 rw root@db1=# execute test;<br>
       ?column?<br>
      ----------<br>
              1<br>
      (1 row)<br>
      <br>
      pg-14.4 rw root@db1=# set application_name='test';<br>
      SET<br>
      pg-14.4 rw root@db1=# select substr(query,1,50) from
      pg_stat_statements;<br>
                        substr<br>
      -------------------------------------------<br>
       prepare test as select /* unique123 */ $1<br>
       select pg_stat_statements_reset()<br>
       set application_name=$1<br>
      (3 rows)<br>
      <br>
      <br>
      =====<br>
      <br>
      pg-14.4 rw root@db1=# set pg_stat_statements.track_utility=off;<br>
      SET<br>
      pg-14.4 rw root@db1=# select pg_stat_statements_reset();<br>
       pg_stat_statements_reset<br>
      --------------------------<br>
      <br>
      (1 row)<br>
      <br>
      pg-14.4 rw root@db1=# prepare test as select /* unique123 */ 1;<br>
      PREPARE<br>
      pg-14.4 rw root@db1=# execute test;<br>
       ?column?<br>
      ----------<br>
              1<br>
      (1 row)<br>
      <br>
      pg-14.4 rw root@db1=# set application_name='test';<br>
      SET<br>
      pg-14.4 rw root@db1=# select substr(query,1,50) from
      pg_stat_statements;<br>
                        substr<br>
      -------------------------------------------<br>
       prepare test as select /* unique123 */ $1<br>
       select pg_stat_statements_reset()<br>
      (2 rows)</font><br>
    <br>
    <br>
    <br>
    <br>
    <br>
    <pre class="moz-signature" cols="72">-- 
Jeremy Schneider
Database Engineer
Amazon Web Services</pre>
  </body>
</html>

--------------n8priadbOn7u9PBtn45XZXG0--