Date: Mon, 4 Nov 2019 11:06:05 -0500
From: Stephen Frost <sfrost@snowman.net>
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>
Subject: Re: auxiliary processes in pg_stat_ssl
Message-ID: <20191104160605.GC6962@tamriel.snowman.net>
References: <20190904151535.GA29108@alvherre.pgsql>
 <20191104132559.GA18441@alvherre.pgsql>
 <CA+Tgmobz8yTDbXMmefKAAUkJ+WpFWQPMy9+GFipbn-NAZuqjtQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="OzJS34+Ao05mLk2R"
Content-Disposition: inline
In-Reply-To: 
 <CA+Tgmobz8yTDbXMmefKAAUkJ+WpFWQPMy9+GFipbn-NAZuqjtQ@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--OzJS34+Ao05mLk2R
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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?
>=20
> 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

--OzJS34+Ao05mLk2R
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJdwExtAAoJEO1sijiDR2RVf9cQAI4q8QYYFDEbMRsjn56FXXdd
0kdbiHdeVbSnYJDHF6ZlLuRLf8gCw29SowR1rexU0qVRgYbuveDYCx4WwB03HE07
PDN3nLonmOOhH/4A9WtpNNh5HQH7M1dUq7coVJEHpQk5YpRDZq33FV3e/NBcUE6/
ZS0pOeEbZIpyJTFho5A9LBHhXwNk/a9adlzca10F5FtMKUQfMW8WqQP19aA60g6e
tS6hEhRb/Nfpau9VAeQCkKKw6yWyybVxOlh1ZREAcsEVdIK1GCxhKr1jiduSQpbu
XiK391vs4zBvMUxhsjgnjyd1WiBJvE1musmi7OWgKpTBkY91+4mkIy3GvzhueX1y
KFzRTiQzPJMRrquLzVyLGxyaHiNKi1klnCCQ0gfoI4Q9w8RCBXp49KNgqfQ3GLOz
4jyR2tbwcs2cXj9LvgP6B9gTE3840nGNrfBABXUQBNu9hNcxVann9MFPUDO0upE1
atuHndnxOEuOA/jDV6/ZToUpPaDYWjtFpBIA2VbFAAhWggDuPIfDwko6bIvxu3VC
6uVBKA7VZeE8oTyQjQpKjW+Q22QTiCMB4V9wIoNd/NqQ17+eChqXSIZitMSUuAMZ
RRZeKWDvXNV7WR6b56vqs0ZjdErQUUnbfuZ4IEh9ZO9/IQoh8y3fFUmQ626NDu5k
CL1A+7ctc6aimuGuPG8m
=FNCD
-----END PGP SIGNATURE-----

--OzJS34+Ao05mLk2R--