Re: proposal: psql: psql variable BACKEND_PID
so 18. 3. 2023 v 16:24 odesílatel Andrew Dunstan napsal: > > On 2023-02-16 Th 23:04, Pavel Stehule wrote: > > > > čt 16. 2. 2023 v 12:49 odesílatel Jelte Fennema napsal: > >> On Thu, 16 Feb 2023 at 12:44, Pavel Stehule >> 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. > ok regards Pavel > > cheers > > > andrew > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com > >
Re: proposal: psql: psql variable BACKEND_PID
On 2023-02-16 Th 23:04, Pavel Stehule wrote: čt 16. 2. 2023 v 12:49 odesílatel Jelte Fennema napsal: On Thu, 16 Feb 2023 at 12:44, Pavel Stehule 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
Re: proposal: psql: psql variable BACKEND_PID
čt 16. 2. 2023 v 12:49 odesílatel Jelte Fennema napsal: > On Thu, 16 Feb 2023 at 12:44, Pavel Stehule > 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 Regards Pavel
Re: proposal: psql: psql variable BACKEND_PID
On Thu, 16 Feb 2023 at 12:44, Pavel Stehule 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.
Re: proposal: psql: psql variable BACKEND_PID
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.
Re: proposal: psql: psql variable BACKEND_PID
po 13. 2. 2023 v 18:52 odesílatel Tom Lane napsal: > Andres Freund 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 >
Re: proposal: psql: psql variable BACKEND_PID
Hi, On 2023-02-13 12:52:23 -0500, Tom Lane wrote: > Andres Freund 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
Re: proposal: psql: psql variable BACKEND_PID
Andres Freund 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
Re: proposal: psql: psql variable BACKEND_PID
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 - 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
Re: proposal: psql: psql variable BACKEND_PID
po 13. 2. 2023 v 18:06 odesílatel Peter Eisentraut < peter.eisentr...@enterprisedb.com> napsal: > On 09.02.23 10:11, Pavel Stehule wrote: > > first and main (for me) - I can use psql variables tab complete - just > > :B - 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? > 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. Regards Pavel
Re: proposal: psql: psql variable BACKEND_PID
On 09.02.23 10:11, Pavel Stehule wrote: first and main (for me) - I can use psql variables tab complete - just :B - 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?
Re: proposal: psql: psql variable BACKEND_PID
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 %:BACKEND_PID:, + but it is safer, because psql variable can be overwriten or unset. or just about any reformulation of that?
Re: proposal: psql: psql variable BACKEND_PID
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 - 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
Re: proposal: psql: psql variable BACKEND_PID
čt 9. 2. 2023 v 9:57 odesílatel Peter Eisentraut < peter.eisentr...@enterprisedb.com> napsal: > 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 > there are 2 (3) my motivations first and main (for me) - I can use psql variables tab complete - just :B - 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. Regards Pavel
Re: proposal: psql: psql variable BACKEND_PID
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
Re: proposal: psql: psql variable BACKEND_PID
po 6. 2. 2023 v 13:03 odesílatel Daniel Verite
napsal:
> 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.
>
I think psql never tries to execute a query if the engine is not connected,
so for usage in queries undefined state is not important - it will be
always defined.
for using in \if is unset may be a better state, because you can try to use
{? varname} syntax.
0 is theoretically valid process id number, so I am not sure if 0 is ok. I
don't know if some numbers can be used like invalid process id?
>
> Best regards,
> --
> Daniel Vérité
> https://postgresql.verite.pro/
> Twitter: @DanielVerite
>
Re: proposal: psql: psql variable BACKEND_PID
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
Re: proposal: psql: psql variable BACKEND_PID
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
Re: proposal: psql: psql variable BACKEND_PID
po 6. 2. 2023 v 0:35 odesílatel Corey Huinker napsal: > 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 > Thank you very much Pavel
Re: proposal: psql: psql variable BACKEND_PID
po 6. 2. 2023 v 0:25 odesílatel Corey Huinker napsal: > >>> >>> Clearly, it is hard to write a regression test for an externally >>> volatile value. `SELECT sign(:BACKEND_PID)` would technically do the job, >>> if we're striving for completeness. >>> >> >> I did simple test - :BACKEND_PID should be equal pg_backend_pid() >> >> > > Even better. > > >> >>> >>> Do we want to change %p to pull from this variable and save the >>> snprintf()? Not a measurable savings, more or a don't-repeat-yourself thing. >>> >> >> I checked the code, and I don't think so. Current state is safer (I >> think). The psql's variables are not protected, and I think, so is safer, >> better to >> read the value for prompt directly by usage of the libpq API instead read >> the possibly "customized" variable. I see possible inconsistency, >> but again, the same inconsistency can be for variables USER and DBNAME >> too, and I am not able to say what is significantly better. Just prompt >> shows >> real value, and the related variable is +/- copy in connection time. >> >> I am not 100% sure in this area what is better, but the change requires >> wider and more general discussion, and I don't think the benefits of >> possible >> change are enough. There are just two possible solutions - we can protect >> some psql's variables (and it can do some compatibility issues), or we >> need to accept, so the value in prompt can be fake. It is better to not >> touch it :-). >> > > I agree it is out of scope of this patch, but I like the idea of protected > psql variables, and I doubt users are intentionally re-using these vars to > any positive effect. The more likely case is that newer psql vars just > happen to use the names chosen by somebody's script in the past. > bash variables are not protected too. I know this is in a different context, and different architecture. It can be a very simple patch, but it needs wider discussion. Probably it should be immutable, it is safer, and now I do not have any useful use case for mutability of these variables. Regards Pavel > > >> >> done >> >> >> > Everything passes. Docs look good. Test looks good. >
Re: 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
Re: proposal: psql: psql variable BACKEND_PID
> > >> >> Clearly, it is hard to write a regression test for an externally volatile >> value. `SELECT sign(:BACKEND_PID)` would technically do the job, if we're >> striving for completeness. >> > > I did simple test - :BACKEND_PID should be equal pg_backend_pid() > > Even better. > >> >> Do we want to change %p to pull from this variable and save the >> snprintf()? Not a measurable savings, more or a don't-repeat-yourself thing. >> > > I checked the code, and I don't think so. Current state is safer (I > think). The psql's variables are not protected, and I think, so is safer, > better to > read the value for prompt directly by usage of the libpq API instead read > the possibly "customized" variable. I see possible inconsistency, > but again, the same inconsistency can be for variables USER and DBNAME > too, and I am not able to say what is significantly better. Just prompt > shows > real value, and the related variable is +/- copy in connection time. > > I am not 100% sure in this area what is better, but the change requires > wider and more general discussion, and I don't think the benefits of > possible > change are enough. There are just two possible solutions - we can protect > some psql's variables (and it can do some compatibility issues), or we > need to accept, so the value in prompt can be fake. It is better to not > touch it :-). > I agree it is out of scope of this patch, but I like the idea of protected psql variables, and I doubt users are intentionally re-using these vars to any positive effect. The more likely case is that newer psql vars just happen to use the names chosen by somebody's script in the past. > > done > > > Everything passes. Docs look good. Test looks good.
Re: proposal: psql: psql variable BACKEND_PID
Hi so 4. 2. 2023 v 21:36 odesílatel Corey Huinker napsal: > with doc and unsetting variable >> >> Regards >> >> Pavel >> >> > Patch applies. > > 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 > > [81:14:57:01 EST] corey=# \echo :BACKEND_PID > 81 > [81:14:57:04 EST] corey=# select 1; > 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. > Time: 1.554 ms > [:15:02:31 EST] !> \echo :BACKEND_PID > :BACKEND_PID > > Clearly, it is hard to write a regression test for an externally volatile > value. `SELECT sign(:BACKEND_PID)` would technically do the job, if we're > striving for completeness. > I did simple test - :BACKEND_PID should be equal pg_backend_pid() > > The inability to easily DISCONNECT via psql, and the deleterious effect > that would have on other regression tests tells me that we can leave that > one untested. > I agree > > Notes: > > 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. > done > Do we want to change %p to pull from this variable and save the > snprintf()? Not a measurable savings, more or a don't-repeat-yourself thing. > I checked the code, and I don't think so. Current state is safer (I think). The psql's variables are not protected, and I think, so is safer, better to read the value for prompt directly by usage of the libpq API instead read the possibly "customized" variable. I see possible inconsistency, but again, the same inconsistency can be for variables USER and DBNAME too, and I am not able to say what is significantly better. Just prompt shows real value, and the related variable is +/- copy in connection time. I am not 100% sure in this area what is better, but the change requires wider and more general discussion, and I don't think the benefits of possible change are enough. There are just two possible solutions - we can protect some psql's variables (and it can do some compatibility issues), or we need to accept, so the value in prompt can be fake. It is better to not touch it :-). > > In the varlistentry, I suggest we add "This variable is unset when the > connection is lost." after "but can be changed or unset. > done Regards Pavel From 042189d20ca1979b2171412a9c9286ea476d59cf Mon Sep 17 00:00:00 2001 From: "ok...@github.com" Date: Sat, 4 Feb 2023 18:29:42 +0100 Subject: [PATCH] implementation of BACKEND_PID psql's variable --- doc/src/sgml/ref/psql-ref.sgml | 16 +++- src/bin/psql/command.c | 4 src/bin/psql/help.c| 2 ++ src/test/regress/expected/psql.out | 7 +++ src/test/regress/sql/psql.sql | 3 +++ 5 files changed, 31 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index dc6528dc11..805b0b1d93 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -3902,6 +3902,17 @@ bar + +BACKEND_PID + + +The id of server process of the current connection. This is set every +time you connect to a database (including program start-up), but can +be changed or unset. This variable is unset when the connection is lost. + + + + COMP_KEYWORD_CASE @@ -4548,7 +4559,10 @@ testdb= INSERT INTO my_table VALUES (:'content'); %p - The process ID of the backend currently connected to. + The process ID of the backend currently connected to. + This substitution is almost equal to using %:BACKEND_PID:, + but it is safer, because psql variable can be overwriten or unset. + diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index b5201edf55..5a9b0e1569 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -3783,6 +3783,9 @@ SyncVariables(void) SetVariable(pset.vars, "PORT", PQport(pset.db)); SetVariable(pset.vars, "ENCODING", pg_encoding_to_char(pset.encoding)); + snprintf(vbuf, sizeof(vbuf), "%d", PQbackendPID(pset.db)); + SetVariable(pset.vars, "BACKEND_PID", vbuf); + /* this bit should match connection_warnings(): */ /* Try to get full text form of version, might include "devel" etc */ server_version = PQparameterStatus(pset.db, "server_version"); @@ -3817,6 +3820,7 @@ UnsyncVariables(void) SetVariable(pset.vars, "ENCODING", NULL); SetVariable(pset.vars, "SERVER_VERSION_NAME", NULL); SetVariable(pset.vars, "SERVER_VERSION_NUM", NULL); +
Re: proposal: psql: psql variable BACKEND_PID
> > with doc and unsetting variable > > Regards > > Pavel > > Patch applies. 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 [81:14:57:01 EST] corey=# \echo :BACKEND_PID 81 [81:14:57:04 EST] corey=# select 1; 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. Time: 1.554 ms [:15:02:31 EST] !> \echo :BACKEND_PID :BACKEND_PID Clearly, it is hard to write a regression test for an externally volatile value. `SELECT sign(:BACKEND_PID)` would technically do the job, if we're striving for completeness. The inability to easily DISCONNECT via psql, and the deleterious effect that would have on other regression tests tells me that we can leave that one untested. Notes: 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. Do we want to change %p to pull from this variable and save the snprintf()? Not a measurable savings, more or a don't-repeat-yourself thing. In the varlistentry, I suggest we add "This variable is unset when the connection is lost." after "but can be changed or unset.
Re: proposal: psql: psql variable BACKEND_PID
Hi
pá 3. 2. 2023 v 20:27 odesílatel Corey Huinker
napsal:
>
>
> On Fri, Feb 3, 2023 at 5:42 AM Pavel Stehule
> wrote:
>
>> Hi
>>
>> 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.
>>
>> Comments, notes?
>>
>> Regards
>>
>> Pavel
>>
>
> Interesting, and probably useful.
>
> It needs a corresponding line in UnsyncVariables():
>
> SetVariable(pset.vars, "BACKEND_PID", NULL);
>
> That will set the variable back to null when the connection goes away.
>
with doc and unsetting variable
Regards
Pavel
>
>
>
>
>
From e883f551227116e87c643d588f8c957f2defaeeb Mon Sep 17 00:00:00 2001
From: "ok...@github.com"
Date: Sat, 4 Feb 2023 18:29:42 +0100
Subject: [PATCH] implementation of BACKEND_PID psql's variable
---
doc/src/sgml/ref/psql-ref.sgml | 11 +++
src/bin/psql/command.c | 4
src/bin/psql/help.c| 2 ++
3 files changed, 17 insertions(+)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11..92180a2eae 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3902,6 +3902,17 @@ bar
+
+BACKEND_PID
+
+
+The id of server process of the current connection.
+This is set every time you connect to a database (including
+program start-up), but can be changed or unset.
+
+
+
+
COMP_KEYWORD_CASE
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b5201edf55..5a9b0e1569 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3783,6 +3783,9 @@ SyncVariables(void)
SetVariable(pset.vars, "PORT", PQport(pset.db));
SetVariable(pset.vars, "ENCODING", pg_encoding_to_char(pset.encoding));
+ snprintf(vbuf, sizeof(vbuf), "%d", PQbackendPID(pset.db));
+ SetVariable(pset.vars, "BACKEND_PID", vbuf);
+
/* this bit should match connection_warnings(): */
/* Try to get full text form of version, might include "devel" etc */
server_version = PQparameterStatus(pset.db, "server_version");
@@ -3817,6 +3820,7 @@ UnsyncVariables(void)
SetVariable(pset.vars, "ENCODING", NULL);
SetVariable(pset.vars, "SERVER_VERSION_NAME", NULL);
SetVariable(pset.vars, "SERVER_VERSION_NUM", NULL);
+ SetVariable(pset.vars, "BACKEND_PID", NULL);
}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index e45c4aaca5..61c6edd0ba 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -396,6 +396,8 @@ helpVariables(unsigned short int pager)
HELP0(" AUTOCOMMIT\n"
"if set, successful SQL commands are automatically committed\n");
+ HELP0(" BACKEND_PID\n"
+ "id of server process of the current connection\n");
HELP0(" COMP_KEYWORD_CASE\n"
"determines the case used to complete SQL key words\n"
"[lower, upper, preserve-lower, preserve-upper]\n");
--
2.39.1
Re: proposal: psql: psql variable BACKEND_PID
On Fri, Feb 3, 2023 at 5:42 AM Pavel Stehule wrote: > Hi > > 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. > > Comments, notes? > > Regards > > Pavel > Interesting, and probably useful. It needs a corresponding line in UnsyncVariables(): SetVariable(pset.vars, "BACKEND_PID", NULL); That will set the variable back to null when the connection goes away.
proposal: psql: psql variable BACKEND_PID
Hi
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.
Comments, notes?
Regards
Pavel
From 153994fd93571964766ca054b0f7fe342ac72a6f Mon Sep 17 00:00:00 2001
From: "ok...@github.com"
Date: Fri, 3 Feb 2023 11:40:41 +0100
Subject: [PATCH] implementation of BACKEND_PID psql's variable
---
src/bin/psql/command.c | 3 +++
src/bin/psql/help.c| 2 ++
2 files changed, 5 insertions(+)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b5201edf55..934dd26c61 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3783,6 +3783,9 @@ SyncVariables(void)
SetVariable(pset.vars, "PORT", PQport(pset.db));
SetVariable(pset.vars, "ENCODING", pg_encoding_to_char(pset.encoding));
+ snprintf(vbuf, sizeof(vbuf), "%d", PQbackendPID(pset.db));
+ SetVariable(pset.vars, "BACKEND_PID", vbuf);
+
/* this bit should match connection_warnings(): */
/* Try to get full text form of version, might include "devel" etc */
server_version = PQparameterStatus(pset.db, "server_version");
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index e45c4aaca5..61c6edd0ba 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -396,6 +396,8 @@ helpVariables(unsigned short int pager)
HELP0(" AUTOCOMMIT\n"
"if set, successful SQL commands are automatically committed\n");
+ HELP0(" BACKEND_PID\n"
+ "id of server process of the current connection\n");
HELP0(" COMP_KEYWORD_CASE\n"
"determines the case used to complete SQL key words\n"
"[lower, upper, preserve-lower, preserve-upper]\n");
--
2.39.1
