Re: pg_stat_activity query_id

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(at)ibu(dot)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