Обсуждение: psql help
Hello:
Following works-
SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0)
and usename = 'DBUSER_10'
and now() - state_change >= interval $a'${TIMEOUT_MINS}'$a
order by now() - state_change >= interval $a'${TIMEOUT_MINS}'$a desc limit 1;
How can I rewrite the above in psql and pg_terminate_backend all pids that meet above criteria (essentially remove limit 1) ?
Thanks!
Note:
I run this in Linux. TIMEOUT_MINS is env variable.
How can I rewrite the above in psql
Sorry, there is no problem with the following statement and the environment variable. It works fine. But it terminates only one PID due to LIMIT 1. I want to terminate all pids that meet this criteria. If I remove LIMIT 1, pg_terminate_backend(pid) will not work as it expects only one pid at a time. So, the question is how to rewrite this psql so it loops through all pids one pid at a time? Thanks in advance for your help.
SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0)
and usename = 'DBUSER_10'
and now() - state_change >= interval $a'${TIMEOUT_MINS}'$a
order by now() - state_change >= interval $a'${TIMEOUT_MINS}'$a desc limit 1;
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Thursday, July 4, 2024 8:17 PM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: psql help
[EXTERNAL] – This message is from an external sender
On Thu, Jul 4, 2024 at 4:56 PM Murthy Nunna <mnunna@fnal.gov> wrote:
How can I rewrite the above in psql
The only real trick is using a psql variable instead of the shell-injection of the environment variable. Use the --set CLI argument to assign the environment variable to a psql variable then refer to it in the query using :'timout_mins'
Removing the limit 1 should be as simple as not typing limit 1 when you bring the query into the psql script.
David J.
On 7/4/24 20:54, Murthy Nunna wrote: > Sorry, there is no problem with the following statement and the > environment variable. It works fine. But it terminates only one PID due > to LIMIT 1. I want to terminate all pids that meet this criteria. If I > remove LIMIT 1, pg_terminate_backend(pid) will not work as it expects > only one pid at a time. So, the question is how to rewrite this psql so > it loops through all pids one pid at a time? Thanks in advance for your > help. From here: https://www.postgresql.org/docs/current/app-psql.html See: \gexec -- Adrian Klaver adrian.klaver@aklaver.com
pg_terminate_backend(pid) will not work as it expects only one pid at a time.
So, the question is how to rewrite this psql so it loops through all pids one pid at a time?
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, July 4, 2024, Murthy Nunna <mnunna@fnal.gov> wrote:
>> pg_terminate_backend(pid) will not work as it expects only one pid at a
>> time.
> Interesting…I wouldn’t expect the function calls to interact that
> way
TBH, my reaction to that was that the OP doesn't understand SQL
semantics. As you previously said, simply removing the LIMIT clause
should work fine. (The ORDER BY looks kinda pointless, too, unless
there are operational constraints we weren't told about.)
There is a question of exactly what "$a'${TIMEOUT_MINS}'$a"
is supposed to mean, but that's independent of the LIMIT issue.
regards, tom lane
On Fri, Jul 05, 2024 at 03:54:56AM +0000, Murthy Nunna wrote:
> Sorry, there is no problem with the following statement and the environment variable. It works fine. But it
terminatesonly one PID due to LIMIT 1. I want to terminate all pids that meet this criteria. If I remove LIMIT 1,
pg_terminate_backend(pid)will not work as it expects only one pid at a time. So, the question is how to rewrite this
psqlso it loops through all pids one pid at a time? Thanks in advance for your help.
>
> SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
> WHERE pid IN (select unnest(pg_blocking_pids(pid)) from pg_stat_activity where cardinality(pg_blocking_pids(pid))
>0)
> and usename = 'DBUSER_10'
> and now() - state_change >= interval $a'${TIMEOUT_MINS}'$a
> order by now() - state_change >= interval $a'${TIMEOUT_MINS}'$a desc limit 1;
Did you try?
I don't see any reason why it wouldn't work with just 'limit 1' removed.
Best regards,
depesz
-----Original Message-----
From: depesz@depesz.com <depesz@depesz.com>
Sent: Friday, July 5, 2024 6:05 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: psql help
[EXTERNAL] – This message is from an external sender
On Fri, Jul 05, 2024 at 03:54:56AM +0000, Murthy Nunna wrote:
> Sorry, there is no problem with the following statement and the environment variable. It works fine. But it terminates only one PID due to LIMIT 1. I want to terminate all pids that meet this criteria. If I remove LIMIT 1, pg_terminate_backend(pid) will not work as it expects only one pid at a time. So, the question is how to rewrite this psql so it loops through all pids one pid at a time? Thanks in advance for your help.
>
> SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity
> WHERE pid IN (select unnest(pg_blocking_pids(pid)) from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0)
> and usename = 'DBUSER_10'
> and now() - state_change >= interval $a'${TIMEOUT_MINS}'$a
> order by now() - state_change >= interval $a'${TIMEOUT_MINS}'$a desc limit 1;
Did you try?
I don't see any reason why it wouldn't work with just 'limit 1' removed.
Best regards,
depesz
Thanks, depesz. I am pretty sure removing “limit 1” should terminate all pids in the result set. I was just being dumb.