Обсуждение: auxiliary processes in pg_stat_ssl
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
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
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
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
Вложения
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
Вложения
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
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
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