Re: pg_stat_activity query_id

I changed my query. "xact_start is not NULL" must show only active queries.
I see my own query with query_id, pwatch2 (
https://github.com/cybertec-postgresql/pgwatch2) queries with query_id, but
application queries (PostgreSQL JDBC Driver) are without query_id.
Any idea why?

Query is:
select application_name, DATE_TRUNC('second', backend_start)
backend_start, DATE_TRUNC('second', query_start) query_start,
DATE_TRUNC('second', now()) now,query_id, left(query, 6) query from
pg_stat_activity where xact_start is not NULL;

Output:

application_name | backend_start | query_start |
now | query_id | query
------------------------+------------------------+------------------------+------------------------+---------------------+--------
PostgreSQL JDBC Driver | 2023-05-12 15:16:53+03 | 2023-05-12 15:22:14+03 |
2023-05-12 15:22:14+03 | | select
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:22:14+03 |
2023-05-12 15:22:14+03 | 3277233299598306329 | select
(2 rows)

application_name | backend_start | query_start |
now | query_id | query
------------------+------------------------+------------------------+------------------------+---------------------+--------
pgwatch2 | 2023-05-12 15:24:30+03 | 2023-05-12 15:24:30+03 |
2023-05-12 15:24:31+03 | 2551699808678778212 | +
| | |
| | +
| | |
| | SELE
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:24:31+03 |
2023-05-12 15:24:31+03 | 3277233299598306329 | select
(2 rows)

application_name | backend_start | query_start |
now | query_id | query
------------------------+------------------------+------------------------+------------------------+---------------------+--------
PostgreSQL JDBC Driver | 2023-05-12 15:01:59+03 | 2023-05-12 15:27:02+03 |
2023-05-12 15:27:02+03 | | select
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:27:02+03 |
2023-05-12 15:27:02+03 | 3277233299598306329 | select
(2 rows)

application_name | backend_start | query_start |
now | query_id | query
------------------+------------------------+------------------------+------------------------+---------------------+--------
pgwatch2 | 2023-05-12 15:30:30+03 | 2023-05-12 15:30:30+03 |
2023-05-12 15:30:31+03 | 2551699808678778212 | +
| | |
| | +
| | |
| | SELE
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:30:31+03 |
2023-05-12 15:30:31+03 | 3277233299598306329 | select
(2 rows)

application_name | backend_start | query_start |
now | query_id | query
------------------------+------------------------+------------------------+------------------------+---------------------+--------
PostgreSQL JDBC Driver | 2023-05-12 15:17:01+03 | 2023-05-12 15:32:30+03 |
2023-05-12 15:32:30+03 | | select
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:32:30+03 |
2023-05-12 15:32:30+03 | 3277233299598306329 | select
(2 rows)

application_name | backend_start | query_start |
now | query_id | query
------------------------+------------------------+------------------------+------------------------+---------------------+--------
PostgreSQL JDBC Driver | 2023-05-12 15:23:53+03 | 2023-05-12 15:37:03+03 |
2023-05-12 15:37:03+03 | | select
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:37:03+03 |
2023-05-12 15:37:03+03 | 3277233299598306329 | select
(2 rows)

application_name | backend_start | query_start |
now | query_id | query
------------------------+------------------------+------------------------+------------------------+---------------------+--------
PostgreSQL JDBC Driver | 2023-05-12 15:16:59+03 | 2023-05-12 15:39:59+03 |
2023-05-12 15:39:59+03 | | select
PostgreSQL JDBC Driver | 2023-05-12 15:16:46+03 | 2023-05-12 15:39:59+03 |
2023-05-12 15:39:59+03 | | select
psql | 2023-05-12 12:17:48+03 | 2023-05-12 15:39:59+03 |
2023-05-12 15:39:59+03 | 3277233299598306329 | select
(3 rows)

br
Kaido

On Thu, 11 May 2023 at 18:35, kaido vaikla <kaido(dot)vaikla(at)gmail(dot)com> wrote:

> @ Norbert Poellmann, question was: why query_id in pg_stat_activity is
> not present
>
> @Erik Wienhold
> *no query executed yet
> queries are running all the time
>
> postgres=# select application_name, query_id, query_start, now() from
> pg_stat_activity order by 1,3;
> application_name | query_id | query_start
> | now
>
> ------------------------+----------------------+-------------------------------+-------------------------------
> | |
> | 2023-05-11 18:24:33.501918+03
> | |
> | 2023-05-11 18:24:33.501918+03
> | |
> | 2023-05-11 18:24:33.501918+03
> | |
> | 2023-05-11 18:24:33.501918+03
> | |
> | 2023-05-11 18:24:33.501918+03
> barman_receive_wal | | 2023-04-17
> 11:28:16.014685+03 | 2023-05-11 18:24:33.501918+03
> IntelliJ IDEA 2021.3.2 | | 2023-05-11
> 17:22:08.979695+03 | 2023-05-11 18:24:33.501918+03
> IntelliJ IDEA 2021.3.2 | | 2023-05-11
> 17:27:57.091788+03 | 2023-05-11 18:24:33.501918+03
> IntelliJ IDEA 2021.3.2 | | 2023-05-11
> 17:28:09.656131+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:12:02.593022+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:12:13.476739+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:12:18.019404+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:12:25.669757+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:12:29.622189+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:12:59.340269+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:13:04.144495+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:39.08955+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:48.76654+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:49.320727+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:50.714262+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:51.364861+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:52.787627+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:55.131559+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:55.492536+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:57.910117+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:58.071606+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:16:58.724558+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:00.634562+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:01.061438+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:01.712551+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:06.828538+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:08.491885+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:09.611567+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:11.008544+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:12.255552+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:12.756551+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:18.639542+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:19.621642+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:22.112542+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:25.797548+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:28.114542+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:30.701552+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:49.579338+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:17:53.456562+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:12.361217+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:12.372564+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:12.382335+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:22.115011+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:36.605997+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:40.055803+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:18:52.370659+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:20:02.559387+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:23:49.297287+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:14.678548+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:18.423307+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:26.151117+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:26.972592+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:31.243297+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:32.276583+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:32.908943+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:33.340826+03 | 2023-05-11 18:24:33.501918+03
> PostgreSQL JDBC Driver | | 2023-05-11
> 18:24:33.50313+03 | 2023-05-11 18:24:33.501918+03
> psql | -2717221524427549609 | 2023-05-11
> 18:24:33.501918+03 | 2023-05-11 18:24:33.501918+03
> (63 rows)
>
>
>
> * compute_query_id=off
> it is on
> postgres=# show compute_query_id;
> compute_query_id
> ------------------
> on
> (1 row)
>
> * compute_query_id=auto and pg_stat_statements (or any other module that
> calculates query_id) is not loaded
> it is loaded, and like you see, one query has query_id
>
> postgres=# show shared_preload_libraries ;
> shared_preload_libraries
> ----------------------------------
> pg_stat_statements, auto_explain
> (1 row)
> postgres=# \dx
> List of installed extensions
> Name | Version | Schema |
> Description
>
> --------------------+---------+------------+------------------------------------------------------------------------
> pg_stat_statements | 1.10 | public | track planning and execution
> statistics of all SQL statements executed
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
> (2 rows)
>
>
> br
> Kaido
>
> On Thu, 11 May 2023 at 16:46, Erik Wienhold <ewie(at)ewie(dot)name> wrote:
>
>> > On 11/05/2023 11:11 CEST kaido vaikla <kaido(dot)vaikla(at)gmail(dot)com> wrote:
>> >
>> > Question, when or why query_id in pg_stat_activity is not present
>> >
>> >
>> > postgres=# select version();
>> > version
>> >
>> ---------------------------------------------------------------------------------------------------------
>> > PostgreSQL 15.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
>> 20150623 (Red Hat 4.8.5-44), 64-bit
>> > (1 row)
>> >
>> > postgres=# select user;
>> > user
>> > ----------
>> > postgres
>> > (1 row)
>> >
>> > postgres=# show compute_query_id;
>> > compute_query_id
>> > ------------------
>> > on
>> > (1 row)
>> >
>> > postgres=# select application_name, query_id from pg_stat_activity
>> order by 1;
>> > application_name | query_id
>> > ------------------------+---------------------
>> > |
>> > |
>> > |
>> > |
>> > |
>> > barman_receive_wal |
>> > IntelliJ IDEA 2023.1.1 |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > PostgreSQL JDBC Driver |
>> > psql | 3408001232671049700
>> > (61 rows)
>>
>> The reason could be any of the following:
>>
>> * no query executed yet
>> * compute_query_id=off
>> * compute_query_id=auto and pg_stat_statements (or any other module that
>> calculates query_id) is not loaded
>>
>> --
>> Erik
>>
>>
>>