Обсуждение: pg_stat_activity query_id

Поиск
Список
Период
Сортировка

pg_stat_activity query_id

От
kaido vaikla
Дата:
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

Re: pg_stat_activity query_id

От
Norbert Poellmann
Дата:
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

Re: pg_stat_activity query_id

От
Erik Wienhold
Дата:
> 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

Re: pg_stat_activity query_id

От
kaido vaikla
Дата:
@ 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


Re: pg_stat_activity query_id

От
Erik Wienhold
Дата:
> 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

Re: pg_stat_activity query_id

От
kaido vaikla
Дата:
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


Re: pg_stat_activity query_id

От
Erik Wienhold
Дата:
> 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

Re: pg_stat_activity query_id

От
kaido vaikla
Дата:
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

Re: pg_stat_activity query_id

От
Erik Wienhold
Дата:
> 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

Re: pg_stat_activity query_id

От
kaido vaikla
Дата:
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