Re: pg_stat_activity query_id - Norbert Poellmann

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Norbert Poellmann <np@ibu.de>
To: kaido vaikla <kaido.vaikla@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_stat_activity query_id
Date: Thu, 11 May 2023 13:47:11 +0200
Message-ID: <ZFzVv66Wpm7WBnXP@mail.ibu.de> (raw)
In-Reply-To: <CA+427g8qBArPuqwZOuYVhdoCQUsdbutR18fG7Gt7+3JunuHsjA@mail.gmail.com>
References: <CA+427g8qBArPuqwZOuYVhdoCQUsdbutR18fG7Gt7+3JunuHsjA@mail.gmail.com>

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

view thread (10+ messages)  latest in thread

Message-ID: <ZFzVv66Wpm7WBnXP@mail.ibu.de>
Permalink:  ../ZFzVv66Wpm7WBnXP@mail.ibu.de/
Also on:    postgresql.org/message-id/ZFzVv66Wpm7WBnXP@mail.ibu.de

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: np@ibu.de, kaido.vaikla@gmail.com
  Subject: Re: pg_stat_activity query_id
  In-Reply-To: <ZFzVv66Wpm7WBnXP@mail.ibu.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox