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.
There are three kinds of variables - there are about 40 psql variables.
I can be mistaken - I thought so somebody if needed filtering in pg_stat_activity, they can run just "\set"
and he can see
(2023-02-13 19:09:10) postgres=# \set
AUTOCOMMIT = 'on'
BACKEND_PID = 10102
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
ERROR = 'false'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HIDE_TOAST_COMPRESSION = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
...
he don't need to search more
To find and use pg_backend_pid is not rocket science. But use :BACKEND_PID is simpler.
It is true, so this information is redundant - I see some benefit in the possibility to see "by using \set" a little bit more complete view on session, but surely - this is in "nice to have" category (from my perspective), and if others has different opinion, than we don't need to spend with this patch more time. This is not an important feature.
Regards
Pavel
regards, tom lane