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

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