Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

[torikoshia]

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