Thread: pg_stat_activity query_id
Hi,
Question, when or why query_id in pg_stat_activity is not present
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
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
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
> 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
*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
> 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
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?
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)
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
Kaido
On Thu, 11 May 2023 at 18:35, kaido vaikla <kaido.vaikla@gmail.com> wrote:
*no query executed yetqueries 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
KaidoOn 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
> 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
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
"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
> 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
Hi
Is it now bug or expected behave. If it is expected, can it mentioned in manual too?
br
Kaido
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