Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1px4lR-00067P-QZ for pgsql-admin@arkaria.postgresql.org; Thu, 11 May 2023 11:47:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1px4lQ-0006Qt-NS for pgsql-admin@arkaria.postgresql.org; Thu, 11 May 2023 11:47:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1px4lQ-0006Qk-Cg for pgsql-admin@lists.postgresql.org; Thu, 11 May 2023 11:47:16 +0000 Received: from mail.ibu.de ([136.243.18.157] helo=ibu.de) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1px4lN-001xtu-0f for pgsql-admin@postgresql.org; Thu, 11 May 2023 11:47:15 +0000 Received: from mail.ibu.de (localhost [127.0.0.1]) by ibu.de (8.16.1/8.16.1) with ESMTPS id 34BBlB3E043318 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NO); Thu, 11 May 2023 13:47:11 +0200 (CEST) (envelope-from np@ibu.de) Received: (from np@localhost) by mail.ibu.de (8.16.1/8.16.1/Submit) id 34BBlBkg043317; Thu, 11 May 2023 13:47:11 +0200 (CEST) (envelope-from np@ibu.de) X-Authentication-Warning: mail.your-server.de: np set sender to np@ibu.de using -f Date: Thu, 11 May 2023 13:47:11 +0200 From: Norbert Poellmann To: kaido vaikla Cc: pgsql-admin@postgresql.org Subject: Re: pg_stat_activity query_id Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: "From: np@ibu.de" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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