Thread: proposal: psql: psql variable BACKEND_PID

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

A small but helpful feature.

The new status of this patch is: Ready for Committer

    Corey Huinker wrote:

> Manually testing confirms that it works, at least for the connected state. I
> don't actually know how get psql to invoke DISCONNECT, so I killed the dev
> server and can confirm

Maybe something like this could be used, with no external action:

  postgres=# \echo :BACKEND_PID
  10805
  postgres=# create user tester superuser;
  CREATE ROLE
  postgres=# \c postgres tester
  You are now connected to database "postgres" as user "tester".
  postgres=# alter user tester nosuperuser connection limit 0;
  ALTER ROLE
  postgres=# select pg_terminate_backend(pg_backend_pid());
  FATAL:  terminating connection due to administrator command
  server closed the connection unexpectedly
      This probably means the server terminated abnormally
      before or while processing the request.
  The connection to the server was lost. Attempting reset: Failed.
  The connection to the server was lost. Attempting reset: Failed.

  !?> \echo :BACKEND_PID
  :BACKEND_PID


> In the varlistentry, I suggest we add "This variable is unset when the
> connection is lost." after "but can be changed or unset.

Personally I'd much rather have BACKEND_PID set to 0 rather than being unset
when not connected. For one thing it allows safely using \if :BACKEND_PID.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

    I wrote:

> > In the varlistentry, I suggest we add "This variable is unset when the
> > connection is lost." after "but can be changed or unset.
>
> Personally I'd much rather have BACKEND_PID set to 0 rather than being unset
> when not connected. For one thing it allows safely using \if :BACKEND_PID.

Oops it turns out that was wishful thinking from me.
\if does not interpret a non-zero integer as true, except for the
value "1".
I'd still prefer BACKEND_PID being 0 when not connected, though.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On 03.02.23 11:41, Pavel Stehule wrote:
> We can simply allow an access to backend process id thru psql variable. 
> I propose the name "BACKEND_PID". The advantages of usage are simple 
> accessibility by command \set, and less typing then using function 
> pg_backend_pid, because psql variables are supported by tab complete 
> routine. Implementation is very simple, because we can use the function 
> PQbackendPID.

What would this be useful for?

You can mostly do this using

     select pg_backend_pid() AS "BACKEND_PID" \gset

Hi,

On 2023-02-09 10:11:21 +0100, Pavel Stehule wrote:
> first and main (for me) - I can use psql variables tab complete - just
> :B<tab> - it is significantly faster
> second - I can see all connection related information by \set
> third - there is not hook on reconnect in psql - so if you implement
> BACKEND_PID by self, you ensure to run query with pg_backend_pid() after
> any reconnect or connection change.
> 
> It is clean so you can run "select pg_backend_pid() AS "BACKEND_PID" \gset"
> and you can store it to .psqlrc. But most of the time I am in customer's
> environment, and I have the time, possibility to do a complete setup of
> .psqlrc. It looks (for me) like a generally useful feature to be
> everywhere.

I personally just solved this by using %p in PROMPT*. Not that that serves
quite the same niche.

I guess the fact that we have %p is a minor precedent of psql special casing
backend pid in psql.

Greetings,

Andres Freund

On 2023-02-04 15:35:58 -0500, Corey Huinker wrote:
> This effectively makes the %p prompt (which I use in the example above) the
> same as %:BACKEND_PID: and we may want to note that in the documentation.

I don't really see much of a point in noting this in the doc.  I don't know in
what situation a user would be helped by reading

+         This substitution is almost equal to using <literal>%:BACKEND_PID:</literal>,
+         but it is safer, because psql variable can be overwriten or unset.

or just about any reformulation of that?

On 09.02.23 10:11, Pavel Stehule wrote:
> first and main (for me) - I can use psql variables tab complete - just 
> :B<tab> - it is significantly faster
> second - I can see all connection related information by \set
> third - there is not hook on reconnect in psql - so if you implement 
> BACKEND_PID by self, you ensure to run query with pg_backend_pid() after 
> any reconnect or connection change.
> 
> It is clean so you can run "select pg_backend_pid() AS "BACKEND_PID" 
> \gset" and you can store it to .psqlrc. But most of the time I am in 
> customer's environment, and I have the time, possibility to do a 
> complete setup of .psqlrc. It looks (for me) like a generally useful 
> feature to be everywhere.

But what do you need the backend PID for in the first place?

Of course, you might want to use it to find your own session in 
pg_stat_activity or something like that, but then you're already in a 
query and can use pg_backend_pid().  What do you need the backend PID 
for outside of such a query?

Hi,

On 2023-02-13 18:06:23 +0100, Peter Eisentraut wrote:
> On 09.02.23 10:11, Pavel Stehule wrote:
> > first and main (for me) - I can use psql variables tab complete - just
> > :B<tab> - it is significantly faster
> > second - I can see all connection related information by \set
> > third - there is not hook on reconnect in psql - so if you implement
> > BACKEND_PID by self, you ensure to run query with pg_backend_pid() after
> > any reconnect or connection change.
> > 
> > It is clean so you can run "select pg_backend_pid() AS "BACKEND_PID"
> > \gset" and you can store it to .psqlrc. But most of the time I am in
> > customer's environment, and I have the time, possibility to do a
> > complete setup of .psqlrc. It looks (for me) like a generally useful
> > feature to be everywhere.
> 
> But what do you need the backend PID for in the first place?

For me it's using gdb, pidstat, strace, perf, ...

But for those %p in the PROMPTs is more useful.


> Of course, you might want to use it to find your own session in
> pg_stat_activity or something like that, but then you're already in a query
> and can use pg_backend_pid().  What do you need the backend PID for outside
> of such a query?

E.g. I fire of a query, it's slower than I'd like, I want to attach perf. Of
course I can establish a separate connection, query pg_stat_activity there,
and then perf. But that requires manually filtering pg_stat_activity to find
the query.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2023-02-13 18:06:23 +0100, Peter Eisentraut wrote:
>> But what do you need the backend PID for in the first place?

> For me it's using gdb, pidstat, strace, perf, ...
> But for those %p in the PROMPTs is more useful.

Indeed, because ...

> E.g. I fire of a query, it's slower than I'd like, I want to attach perf. Of
> course I can establish a separate connection, query pg_stat_activity there,
> and then perf. But that requires manually filtering pg_stat_activity to find
> the query.

... in this case, the problem is that the session is tied up doing the
slow query.  You can't run "select pg_backend_pid()", but you can't
extract a psql variable value either.  If you had the foresight to
set up a PROMPT, or to collect the PID earlier, you're good.  But I'm
still not seeing where a psql variable makes that easier.

I don't buy Pavel's argument that adding Yet Another built-in variable
adds ease of use.  I think what it mostly adds is clutter.  I realize
that "psql --help=variables | wc" is already 160+ lines, but that
doesn't mean that making it longer and longer is a net improvement.

            regards, tom lane

Hi,

On 2023-02-13 12:52:23 -0500, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > E.g. I fire of a query, it's slower than I'd like, I want to attach perf. Of
> > course I can establish a separate connection, query pg_stat_activity there,
> > and then perf. But that requires manually filtering pg_stat_activity to find
> > the query.
> 
> ... in this case, the problem is that the session is tied up doing the
> slow query.  You can't run "select pg_backend_pid()", but you can't
> extract a psql variable value either.  If you had the foresight to
> set up a PROMPT, or to collect the PID earlier, you're good.  But I'm
> still not seeing where a psql variable makes that easier.

I guess you could argue that referencing BACKEND_PID in PROMPT would be more
readable. But that's about it.

Greetings,

Andres Freund

On 13.02.23 18:33, Pavel Stehule wrote:
> In every real use case you can use pg_backend_pid(), but you need to 
> write a complete name without tab complete, and you need to know so this 
> function is available.
> 
> BACKEND_PID is supported by  tab complete, and it is displayed in \set 
> list and \? variables. Nothing less, nothing more, Custom psql variable 
> can have some obsolete value.
> 
> I can imagine using :BACKEND_PID in \echo command - and it just saves 
> you one step with its own custom variable.
> 
> It is just some more comfort with almost zero cost.

This line of argument would open us up to copying just about every bit 
of session state into psql just to make it slightly easier to use.

On Thu, 16 Feb 2023 at 12:44, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> To find and use pg_backend_pid is not rocket science. But use :BACKEND_PID is simpler.

I wanted to call out that if there's a connection pooler (e.g.
PgBouncer) in the middle, then BACKEND_PID (and %p) are incorrect, but
pg_backend_pid() would work for the query. This might be an edge case,
but if BACKEND_PID is added it might be worth listing this edge case
in the docs somewhere.

čt 16. 2. 2023 v 12:49 odesílatel Jelte Fennema <me@jeltef.nl> napsal:

On Thu, 16 Feb 2023 at 12:44, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> To find and use pg_backend_pid is not rocket science. But use :BACKEND_PID is simpler.

I wanted to call out that if there's a connection pooler (e.g.
PgBouncer) in the middle, then BACKEND_PID (and %p) are incorrect, but
pg_backend_pid() would work for the query. This might be an edge case,
but if BACKEND_PID is added it might be worth listing this edge case
in the docs somewhere.

good note

This patch is marked RFC, but given the comments upthread from Tom, Andres and Peter, I think it should actually be Rejected.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com