MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Tue, 28 Jul 2020 17:07:02 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Atsushi Torikoshi <atorik@gmail.com>, Tatsuro Yamada
 <tatsuro.yamada.tf@nttcom.co.jp>, Robert Haas <robertmhaas@gmail.com>, Tomas
 Vondra <tomas.vondra@2ndquadrant.com>, Michael Paquier
 <michael@paquier.xyz>, Bruce Momjian <bruce@momjian.us>, Evgeny Efimkin
 <efimkin@yandex-team.ru>, PostgreSQL Hackers
 <pgsql-hackers@lists.postgresql.org>
Subject: Re: Feature improvement: can we add queryId for
 pg_catalog.pg_stat_activity view?
In-Reply-To: <20200714112453.hxxbwobyimefxzv7@nol>
References: 
 <CAOBaU_ZV5JSxx-OjMmESApBnE4ODm=E8zU5_n5yf0CP8Wn2vFQ@mail.gmail.com>
 <CA+Tgmoavept8pK_8pPBptsJn=oTUtF9-caEOG0AqaUHYi7Ycqw@mail.gmail.com>
 <20200207101250.GA10539@nol>
 <CAOBaU_atY814+Wy6e+Ut2AhG1nPSAqYyUvKDpHXahgkOthfpGg@mail.gmail.com>
 <20200314175351.pihoihvko3xgywdn@nol> <20200316144312.sxg4zwbkfmdedh36@nol>
 <20200402132506.z43sg7ferqgmpthz@nol>
 <57df2441-f036-ba75-a374-c2e993015376@nttcom.co.jp_1>
 <CAOBaU_aRWVYoUdQ1i2DW_RqM8xACvWK2zyyucewHZfaqxjhQ_g@mail.gmail.com>
 <CACZ0uYFTCdujJc65qwGsotAGGGs5YROPbeFxMQBLz6ZcevmJBQ@mail.gmail.com>
 <20200714112453.hxxbwobyimefxzv7@nol>
Message-ID: <a49fdf2ae0e3c22e1790c9bdb9cc520c@oss.nttdata.com>
User-Agent: Roundcube Webmail/1.1.9
Precedence: bulk

On 2020-07-14 20:24, Julien Rouhaud wrote:
> On Tue, Jul 14, 2020 at 07:11:02PM +0900, Atsushi Torikoshi wrote:
>> Hi,
>> 
>> v9 patch fails to apply to HEAD, could you check and rebase it?
> 
> Thanks for the notice, v10 attached!
> 
>> And here are minor typos.
>> 
>>  79 +        * utility statements.  Note that we don't compute a 
>> queryId
>> for prepared
>>  80 +        * statemets related utility, as those will inherit from 
>> the
>> underlying
>>  81 +        * statements's one (except DEALLOCATE which is entirely
>> untracked).
>> 
>> statemets -> statements
>> statements's -> statements' or statement's?
> 
> Thanks!  I went with "statement's".

Thanks for updating!
I tested the patch setting log_statement = 'all', but %Q in 
log_line_prefix
was always 0 even when pg_stat_statements.queryid and
pg_stat_activity.queryid are not 0.

Is this an intentional behavior?


```
   $ initdb --no-locale -D data


   $ edit postgresql.conf
     shared_preload_libraries = 'pg_stat_statements'
     logging_collector = on
     log_line_prefix = '%m [%p] queryid:%Q '
     log_statement = 'all'

   $ pg_ctl start -D data

   $ psql
   =# CREATE EXTENSION pg_stat_statements;

   =# CREATE TABLE t1 (i int);
   =# INSERT INTO t1 VALUES (0),(1);
   =# SELECT queryid, query FROM pg_stat_activity;

   -- query ids are all 0 on the log
   $ view log
   2020-07-28 15:57:58.475 EDT [4480] queryid:0 LOG:  statement: CREATE 
TABLE t1 (i int);
   2020-07-28 15:58:13.730 EDT [4480] queryid:0 LOG:  statement: INSERT 
INTO t1 VALUES (0),(1);
   2020-07-28 15:59:28.389 EDT [4480] queryid:0 LOG:  statement: SELECT * 
FROM t1;

   -- on pg_stat_activity and pgss, query ids are not 0
   $ psql
   =# SELECT queryid, query FROM pg_stat_activity WHERE query LIKE 
'%t1%';
          queryid        |                                query
   
----------------------+----------------------------------------------------------------------
     1109063694563750779 | SELECT * FROM t1;
    -2582225123719476948 | SELECT queryid, query FROM pg_stat_activity 
WHERE query LIKE '%t1%';
   (2 rows)

   =# SELECT queryid, query FROM pg_stat_statements WHERE query LIKE 
'%t1%';
          queryid        |              query
   ----------------------+---------------------------------
    -5028988130796701553 | CREATE TABLE t1 (i int)
     1109063694563750779 | SELECT * FROM t1
     2726469050076420724 | INSERT INTO t1 VALUES ($1),($2)

```


And here is a minor typo.
  optionnally -> optionally


> 753 +       /* query identifier, optionnally computed using 
> post_parse_analyze_hook */


Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION