Em qua., 4 de set. de 2019 às 12:15, Alvaro Herrera
<alvhe...@2ndquadrant.com> escreveu:
>
> I just noticed that we list auxiliary processes in pg_stat_ssl:
>
> 55432 13devel 28627=# select * from pg_stat_ssl ;
> pid │ ssl │ version │ cipher │ bits │ compression │
> client_dn │ client_serial │ issuer_dn
> ───────┼─────┼─────────┼────────────────────────┼──────┼─────────────┼───────────┼───────────────┼───────────
> 28618 │ f │ │ │ │ │
> │ │
> 28620 │ f │ │ │ │ │
> │ │
> 28627 │ t │ TLSv1.3 │ TLS_AES_256_GCM_SHA384 │ 256 │ f │
> │ │
> 28616 │ f │ │ │ │ │
> │ │
> 28615 │ f │ │ │ │ │
> │ │
> 28617 │ f │ │ │ │ │
> │ │
> (6 filas)
>
> 55432 13devel 28627=# select pid, backend_type from pg_stat_activity ;
> pid │ backend_type
> ───────┼──────────────────────────────
> 28618 │ autovacuum launcher
> 28620 │ logical replication launcher
> 28627 │ client backend
> 28616 │ background writer
> 28615 │ checkpointer
> 28617 │ walwriter
> (6 filas)
>
> But this seems pointless. Should we not hide those? Seems this only
> happened as an unintended side-effect of fc70a4b0df38. It appears to me
> that we should redefine that view to restrict backend_type that's
> 'client backend' (maybe include 'wal receiver'/'wal sender' also, not
> sure.)
>
Yep, it is pointless. BackendType that open connections to server are:
autovacuum worker, client backend, background worker, wal sender. I
also notice that pg_stat_gssapi is in the same boat as pg_stat_ssl and
we should constraint the rows to backend types that open connections.
I'm attaching a patch to list only connections in those system views.
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
From aca42a2a3dc95fa2b4a1e6a1960d5cc834850034 Mon Sep 17 00:00:00 2001
From: Euler Taveira <eu...@timbira.com.br>
Date: Mon, 4 Nov 2019 14:45:38 +0000
Subject: [PATCH] Show only rows that open connections in some system views
It is pointless to show auxiliary processes that do not open connections
to Postgres in pg_stat_ssl and pg_stat_gssapi. This change affects
compatibility with previous versions.
---
src/backend/catalog/system_views.sql | 6 ++++--
src/test/regress/expected/rules.out | 6 ++++--
2 files changed, 8 insertions(+), 4 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9fe4a47..ce39808 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -826,7 +826,8 @@ CREATE VIEW pg_stat_ssl AS
S.ssl_client_dn AS client_dn,
S.ssl_client_serial AS client_serial,
S.ssl_issuer_dn AS issuer_dn
- FROM pg_stat_get_activity(NULL) AS S;
+ FROM pg_stat_get_activity(NULL) AS S
+ WHERE S.client_port IS NOT NULL;
CREATE VIEW pg_stat_gssapi AS
SELECT
@@ -834,7 +835,8 @@ CREATE VIEW pg_stat_gssapi AS
S.gss_auth AS gss_authenticated,
S.gss_princ AS principal,
S.gss_enc AS encrypted
- FROM pg_stat_get_activity(NULL) AS S;
+ FROM pg_stat_get_activity(NULL) AS S
+ WHERE S.client_port IS NOT NULL;
CREATE VIEW pg_replication_slots AS
SELECT
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 210e9cd..14e7214 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1845,7 +1845,8 @@ pg_stat_gssapi| SELECT s.pid,
s.gss_auth AS gss_authenticated,
s.gss_princ AS principal,
s.gss_enc AS encrypted
- FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc);
+ FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc)
+ WHERE (s.client_port IS NOT NULL);
pg_stat_progress_cluster| SELECT s.pid,
s.datid,
d.datname,
@@ -1964,7 +1965,8 @@ pg_stat_ssl| SELECT s.pid,
s.ssl_client_dn AS client_dn,
s.ssl_client_serial AS client_serial,
s.ssl_issuer_dn AS issuer_dn
- FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc);
+ FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc)
+ WHERE (s.client_port IS NOT NULL);
pg_stat_subscription| SELECT su.oid AS subid,
su.subname,
st.pid,
--
2.7.4
