psql help

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.

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<mailto: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

The convention here is to in-line replies, or bottom-post. Top-posting
makes the archives more difficult to read.

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…maybe try putting the select query into a plpgsql DO command loop and
then call pg_terminate_backend once per loop iteration.

So, the question is how to rewrite this psql so it loops through all pids
one pid at a time?

I can’t tell if you really mean the psql program or are misusing the term

to mean something different…the code you wrote doesn’t seem like it would
execute in psql.

David J.

"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 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

-----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.