Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view? - Mailing list pgsql-hackers

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