pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_stat_activity query_id
10+ messages / 3 participants
[nested] [flat]
* pg_stat_activity query_id
@ 2023-05-11 09:11 kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 11:47 ` Re: pg_stat_activity query_id Norbert Poellmann <np@ibu.de>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 10+ messages in thread
From: kaido vaikla @ 2023-05-11 09:11 UTC (permalink / raw)
To: pgsql-admin
Hi,
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)
br
Kaido
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
@ 2023-05-11 11:47 ` Norbert Poellmann <np@ibu.de>
1 sibling, 0 replies; 10+ messages in thread
From: Norbert Poellmann @ 2023-05-11 11:47 UTC (permalink / raw)
To: kaido vaikla <kaido.vaikla@gmail.com>; +Cc: pgsql-admin
On Thu, May 11, 2023 at 12:11:28PM +0300, kaido vaikla wrote:
Hi Kaido,
the NULL values in application_name stemm from LEFT JOINs
in the definition of view pg_stat_activity, where the right side
(of the join) joined table has no value.
A:
select * from pg_stat_activity;
shows columns wait_event and backend_type
values at records where application_name is NULL.
Details:
-- definition of pg_stat_activity:
db =# \d+ pg_stat_activity
View "pg_catalog.pg_stat_activity"
...
View definition:
SELECT s.datid,
d.datname,
....
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, ..... , leader_pid, query_id)
LEFT JOIN pg_database d ON s.datid = d.oid
LEFT JOIN pg_authid u ON s.usesysid = u.oid;
pg_stat_get_activity(...) as s is one of the joined tables:
db =# select datid, pid, usesysid, application_name, wait_event, backend_type from pg_stat_get_activity(null);
datid | pid | usesysid | application_name | wait_event | backend_type
-------+-------+----------+------------------+---------------------+------------------------------
| 6629 | | | AutoVacuumMain | autovacuum launcher
| 6630 | 10 | | LogicalLauncherMain | logical replication launcher
16390 | 67617 | 16384 | psql | | client backend
| 6626 | | | BgWriterHibernate | background writer
| 6625 | | | CheckpointerMain | checkpointer
| 6628 | | | WalWriterMain | walwriter
In this table (yielded by function pg_stat_get_activity())
datid is null for background worker processes, as you can also see them in a
unix PS(1) process status listing.
cheers
Norbert Poellmann
--
Norbert Poellmann EDV-Beratung email : np@ibu.de
Severinstrasse 5 telefon: 089 38469995
81541 Muenchen, Germany telefon: 0179 2133436
> Hi,
>
> 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)
>
> br
> Kaido
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
@ 2023-05-11 13:46 ` Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Erik Wienhold @ 2023-05-11 13:46 UTC (permalink / raw)
To: kaido vaikla <kaido.vaikla@gmail.com>; pgsql-admin
> On 11/05/2023 11:11 CEST kaido vaikla <kaido.vaikla@gmail.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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
@ 2023-05-11 15:35 ` kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 15:54 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-12 13:34 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: kaido vaikla @ 2023-05-11 15:35 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; np@ibu.de; +Cc: pgsql-admin
@ 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@ewie.name> wrote:
> > On 11/05/2023 11:11 CEST kaido vaikla <kaido.vaikla@gmail.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
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
@ 2023-05-11 15:54 ` Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 10+ messages in thread
From: Erik Wienhold @ 2023-05-11 15:54 UTC (permalink / raw)
To: kaido vaikla <kaido.vaikla@gmail.com>; +Cc: pgsql-admin
> On 11/05/2023 17:35 CEST kaido vaikla <kaido.vaikla@gmail.com> wrote:
>
> * compute_query_id=off
> it is onpostgres=# show compute_query_id;
> compute_query_id
> ------------------
> on
> (1 row)
Is compute_query_id=on configured globally in postgresql.conf or only set for
specific databases and users? Please check pg_catalog.pg_db_role_setting.
Any chance that the JDBC connections set compute_query_id=off?
--
Erik
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
@ 2023-05-12 13:34 ` kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 14:17 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
1 sibling, 1 reply; 10+ messages in thread
From: kaido vaikla @ 2023-05-12 13:34 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; np@ibu.de; +Cc: pgsql-admin
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.vaikla@gmail.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@ewie.name> wrote:
>
>> > On 11/05/2023 11:11 CEST kaido vaikla <kaido.vaikla@gmail.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
>>
>>
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 13:34 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
@ 2023-05-12 14:17 ` Erik Wienhold <ewie@ewie.name>
2023-05-22 13:44 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Erik Wienhold @ 2023-05-12 14:17 UTC (permalink / raw)
To: kaido vaikla <kaido.vaikla@gmail.com>; +Cc: pgsql-admin
> On 12/05/2023 15:34 CEST kaido vaikla <kaido.vaikla@gmail.com> wrote:
>
> 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?
As I wrote in my previous message, pg_catalog.pg_db_role_setting will tell you
if compute_query_id is set for any user/database combination which overrides
the system-wide default from postgresql.conf for new sessions.
Your query results don't include usernames and databases. I assume the JDBC
connections are with a different user or on a different database than the psql
and pgwatch2 connections for which query_id is calculated.
Could be that postgresql.conf sets compute_query_id=off and only the users that
you used for psql and pgwatch2 have compute_query_id={on,auto}.
Then there's also the chance that the applications using the JDBC driver set
compute_query_id=off before sending queries. Doesn't explain however why this
would also be the case for that one connection from IntelliJ IDEA (which I
assume uses JDBC as well). I doubt that JDBC sets compute_query_id=off by
default.
My guess is user-specific session defaults.
--
Erik
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 13:34 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 14:17 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
@ 2023-05-22 13:44 ` kaido vaikla <kaido.vaikla@gmail.com>
2023-05-22 17:43 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 10+ messages in thread
From: kaido vaikla @ 2023-05-22 13:44 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin
I asked from pg jdbc community. Answer was :
"One thing to note is that the driver uses extended query protocol so the
queries are not identical.".
I don't know, is it this now key to understand this issue?
https://github.com/pgjdbc/pgjdbc/discussions/2902#discussioncomment-5917360
br
Kaido
On Fri, 12 May 2023 at 17:17, Erik Wienhold <ewie@ewie.name> wrote:
> > On 12/05/2023 15:34 CEST kaido vaikla <kaido.vaikla@gmail.com> wrote:
> >
> > 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?
>
> As I wrote in my previous message, pg_catalog.pg_db_role_setting will tell
> you
> if compute_query_id is set for any user/database combination which
> overrides
> the system-wide default from postgresql.conf for new sessions.
>
> Your query results don't include usernames and databases. I assume the
> JDBC
> connections are with a different user or on a different database than the
> psql
> and pgwatch2 connections for which query_id is calculated.
>
> Could be that postgresql.conf sets compute_query_id=off and only the users
> that
> you used for psql and pgwatch2 have compute_query_id={on,auto}.
>
> Then there's also the chance that the applications using the JDBC driver
> set
> compute_query_id=off before sending queries. Doesn't explain however why
> this
> would also be the case for that one connection from IntelliJ IDEA (which I
> assume uses JDBC as well). I doubt that JDBC sets compute_query_id=off by
> default.
>
> My guess is user-specific session defaults.
>
> --
> Erik
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 13:34 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 14:17 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-22 13:44 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
@ 2023-05-22 17:43 ` Erik Wienhold <ewie@ewie.name>
2023-05-23 07:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Erik Wienhold @ 2023-05-22 17:43 UTC (permalink / raw)
To: kaido vaikla <kaido.vaikla@gmail.com>; +Cc: pgsql-admin
> On 22/05/2023 15:44 CEST kaido vaikla <kaido.vaikla@gmail.com> wrote:
>
> I asked from pg jdbc community. Answer was :
> "One thing to note is that the driver uses extended query protocol so the
> queries are not identical.".
> I don't know, is it this now key to understand this issue?
> https://github.com/pgjdbc/pgjdbc/discussions/2902#discussioncomment-5917360
Look's like the extended query protocol is the reason for that. Testing with
psycopg 3.1 (which added pipeline mode to use the extended query protocol)
confirms this:
from psycopg import connect
from psycopg.rows import dict_row
conninfo = 'dbname=postgres'
query = 'SELECT 1'
with connect(conninfo) as con0:
backend_pid = con0.info.backend_pid
server_version = con0.info.server_version
print(f"{backend_pid=} {server_version=}")
con0.execute('SET compute_query_id = on')
print("=> simple query")
con0.execute(query)
with connect(conninfo, row_factory=dict_row) as con1:
with con1.execute('''
SELECT pid, query, query_id
FROM pg_stat_activity
WHERE pid = %s
''', (backend_pid,)) as cur:
for row in cur.fetchall():
print(row)
print("=> extended query")
with con0.pipeline():
con0.execute(query)
with connect(conninfo, row_factory=dict_row) as con1:
with con1.execute('''
SELECT pid, query, query_id
FROM pg_stat_activity
WHERE pid = %s
''', (backend_pid,)) as cur:
for row in cur.fetchall():
print(row)
Gives me:
backend_pid=800121 server_version=150002
=> simple query
{'pid': 800121, 'query': 'SELECT 1', 'query_id': 1147616880456321454}
=> extended query
{'pid': 800121, 'query': 'SELECT 1', 'query_id': None}
I wonder if this is a limitation of the extended query protocol. Computing the
query identifier for a prepared statement with placeholders is not very useful.
But I would think that a useful query identifier can be calculated once the
placeholders are bound to concrete values and the query is executed.
--
Erik
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_stat_activity query_id
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 13:46 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 13:34 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 14:17 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
2023-05-22 13:44 ` Re: pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-22 17:43 ` Re: pg_stat_activity query_id Erik Wienhold <ewie@ewie.name>
@ 2023-05-23 07:35 ` kaido vaikla <kaido.vaikla@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: kaido vaikla @ 2023-05-23 07:35 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin
Hi
Is it now bug or expected behave. If it is expected, can it mentioned in
manual too?
br
Kaido
On Mon, 22 May 2023 at 20:43, Erik Wienhold <ewie@ewie.name> wrote:
> > On 22/05/2023 15:44 CEST kaido vaikla <kaido.vaikla@gmail.com> wrote:
> >
> > I asked from pg jdbc community. Answer was :
> > "One thing to note is that the driver uses extended query protocol so the
> > queries are not identical.".
> > I don't know, is it this now key to understand this issue?
> >
> https://github.com/pgjdbc/pgjdbc/discussions/2902#discussioncomment-5917360
>
> Look's like the extended query protocol is the reason for that. Testing
> with
> psycopg 3.1 (which added pipeline mode to use the extended query protocol)
> confirms this:
>
> from psycopg import connect
> from psycopg.rows import dict_row
>
> conninfo = 'dbname=postgres'
> query = 'SELECT 1'
>
> with connect(conninfo) as con0:
> backend_pid = con0.info.backend_pid
> server_version = con0.info.server_version
>
> print(f"{backend_pid=} {server_version=}")
>
> con0.execute('SET compute_query_id = on')
>
> print("=> simple query")
>
> con0.execute(query)
>
> with connect(conninfo, row_factory=dict_row) as con1:
> with con1.execute('''
> SELECT pid, query, query_id
> FROM pg_stat_activity
> WHERE pid = %s
> ''', (backend_pid,)) as cur:
> for row in cur.fetchall():
> print(row)
>
> print("=> extended query")
>
> with con0.pipeline():
> con0.execute(query)
>
> with connect(conninfo, row_factory=dict_row) as con1:
> with con1.execute('''
> SELECT pid, query, query_id
> FROM pg_stat_activity
> WHERE pid = %s
> ''', (backend_pid,)) as cur:
> for row in cur.fetchall():
> print(row)
>
> Gives me:
>
> backend_pid=800121 server_version=150002
> => simple query
> {'pid': 800121, 'query': 'SELECT 1', 'query_id':
> 1147616880456321454}
> => extended query
> {'pid': 800121, 'query': 'SELECT 1', 'query_id': None}
>
> I wonder if this is a limitation of the extended query protocol.
> Computing the
> query identifier for a prepared statement with placeholders is not very
> useful.
> But I would think that a useful query identifier can be calculated once the
> placeholders are bound to concrete values and the query is executed.
>
> --
> Erik
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-05-23 07:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-11 09:11 pg_stat_activity query_id kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 11:47 ` Norbert Poellmann <np@ibu.de>
2023-05-11 13:46 ` Erik Wienhold <ewie@ewie.name>
2023-05-11 15:35 ` kaido vaikla <kaido.vaikla@gmail.com>
2023-05-11 15:54 ` Erik Wienhold <ewie@ewie.name>
2023-05-12 13:34 ` kaido vaikla <kaido.vaikla@gmail.com>
2023-05-12 14:17 ` Erik Wienhold <ewie@ewie.name>
2023-05-22 13:44 ` kaido vaikla <kaido.vaikla@gmail.com>
2023-05-22 17:43 ` Erik Wienhold <ewie@ewie.name>
2023-05-23 07:35 ` kaido vaikla <kaido.vaikla@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox