pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedauxiliary processes in pg_stat_ssl
8+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2019-11-12 c5e8ea978d pg_stat_activity: document client_port being null
2019-11-12 5c46e7d82e pg_stat_{ssl,gssapi}: Show only processes with connections
* auxiliary processes in pg_stat_ssl
@ 2019-09-04 15:15 Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 8+ messages in thread
From: Alvaro Herrera @ 2019-09-04 15:15 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
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.)
--
Álvaro Herrera http://www.twitter.com/alvherre
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-04 13:25 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-11-04 13:25 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Robert Haas <robertmhaas@gmail.com>; Kuntal Ghosh <kuntalghosh.2007@gmail.com>
On 2019-Sep-04, Alvaro Herrera wrote:
> I just noticed that we list auxiliary processes in pg_stat_ssl:
[...]
> 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.)
[crickets]
Robert, Kuntal, any opinion on this?
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-04 14:28 Robert Haas <robertmhaas@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Haas @ 2019-11-04 14:28 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Kuntal Ghosh <kuntalghosh.2007@gmail.com>
On Mon, Nov 4, 2019 at 8:26 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Sep-04, Alvaro Herrera wrote:
> > I just noticed that we list auxiliary processes in pg_stat_ssl:
> [...]
> > 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.)
>
> [crickets]
>
> Robert, Kuntal, any opinion on this?
I think if I were doing something about it, I'd probably try to filter
on a field that directly represents whether there is a connection,
rather than checking the backend type. That way, if the list of
backend types that have client connections changes later, there's
nothing to update. Like "WHERE client_port IS NOT NULL," or something
of that sort.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-04 15:39 Euler Taveira <euler@timbira.com.br>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 2 replies; 8+ messages in thread
From: Euler Taveira @ 2019-11-04 15:39 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Em qua., 4 de set. de 2019 às 12:15, Alvaro Herrera
<alvherre@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
Attachments:
0001-Show-only-rows-that-open-connections-in-some-system-.patch (text/x-patch, 3.7K) download
From aca42a2a3dc95fa2b4a1e6a1960d5cc834850034 Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler@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
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-04 16:06 Stephen Frost <sfrost@snowman.net>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2019-11-04 16:06 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Kuntal Ghosh <kuntalghosh.2007@gmail.com>
Greetings,
* Robert Haas (robertmhaas@gmail.com) wrote:
> On Mon, Nov 4, 2019 at 8:26 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > On 2019-Sep-04, Alvaro Herrera wrote:
> > > I just noticed that we list auxiliary processes in pg_stat_ssl:
> > [...]
> > > 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.)
> >
> > [crickets]
> >
> > Robert, Kuntal, any opinion on this?
>
> I think if I were doing something about it, I'd probably try to filter
> on a field that directly represents whether there is a connection,
> rather than checking the backend type. That way, if the list of
> backend types that have client connections changes later, there's
> nothing to update. Like "WHERE client_port IS NOT NULL," or something
> of that sort.
Yeah, using a "this has a connection" would be better and, as also noted
on this thread, pg_stat_gssapi should get similar treatment.
Based on what we claim in our docs, it does look like 'client_port IS
NOT NULL' should work. I do think we might want to update the docs to
make it a bit more explicit, what we say now is:
TCP port number that the client is using for communication with this
backend, or -1 if a Unix socket is used
We don't explain there that NULL means the backend doesn't have an
external connection even though plenty of those entries show up in every
instance of PG. Perhaps we should add this:
If this field is null, it indicates that this is an internal process
such as autovacuum.
Which is what we say for 'client_addr'.
I have to admit that while it's handy that we just shove '-1' into
client_port when it's a unix socket, it's kind of ugly from a data
perspective- in a green field it'd probably be better to have a
"connection type" field that then indicates which other fields are valid
instead of having a special constant, but that ship sailed long ago and
it's not like we have a lot of people complaining about it, so I suppose
just using it here as suggested is fine.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-05 05:29 Kuntal Ghosh <kuntalghosh.2007@gmail.com>
parent: Euler Taveira <euler@timbira.com.br>
1 sibling, 0 replies; 8+ messages in thread
From: Kuntal Ghosh @ 2019-11-05 05:29 UTC (permalink / raw)
To: Euler Taveira <euler@timbira.com.br>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Alvaro Herrera <alvherre@2ndquadrant.com>
On Mon, Nov 4, 2019 at 9:09 PM Euler Taveira <euler@timbira.com.br> wrote:
> >
> > 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.
>
Yeah, We should hide those. As Robert mentioned, I think checking
whether 'client_port IS NOT NULL' is a better approach than checking
the backend_type. The patch looks good to me.
--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-12 21:49 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Euler Taveira <euler@timbira.com.br>
1 sibling, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2019-11-12 21:49 UTC (permalink / raw)
To: Euler Taveira <euler@timbira.com.br>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 2019-Nov-04, Euler Taveira wrote:
> 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.
Thanks! I pushed this.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: auxiliary processes in pg_stat_ssl
@ 2019-11-12 21:50 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2019-11-12 21:50 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Robert Haas <robertmhaas@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Kuntal Ghosh <kuntalghosh.2007@gmail.com>
On 2019-Nov-04, Stephen Frost wrote:
> Based on what we claim in our docs, it does look like 'client_port IS
> NOT NULL' should work. I do think we might want to update the docs to
> make it a bit more explicit, what we say now is:
>
> TCP port number that the client is using for communication with this
> backend, or -1 if a Unix socket is used
>
> We don't explain there that NULL means the backend doesn't have an
> external connection even though plenty of those entries show up in every
> instance of PG. Perhaps we should add this:
>
> If this field is null, it indicates that this is an internal process
> such as autovacuum.
>
> Which is what we say for 'client_addr'.
Seems sensible. Done. Thanks
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-11-12 21:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-04 15:15 auxiliary processes in pg_stat_ssl Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-04 13:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-04 14:28 ` Robert Haas <robertmhaas@gmail.com>
2019-11-04 16:06 ` Stephen Frost <sfrost@snowman.net>
2019-11-12 21:50 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-04 15:39 ` Euler Taveira <euler@timbira.com.br>
2019-11-05 05:29 ` Kuntal Ghosh <kuntalghosh.2007@gmail.com>
2019-11-12 21:49 ` Alvaro Herrera <alvherre@2ndquadrant.com>
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