From: Murthy Nunna <mnunna@fnal.gov>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: psql help
Thread-Topic: psql help
Thread-Index: AdrObNqxB6eTqF8TSq6ApifyEYKoJw==
Date: Thu, 4 Jul 2024 23:56:03 +0000
Message-ID:
 <DM8PR09MB6677C5B4FDBDFC0F82298DC7B8DE2@DM8PR09MB6677.namprd09.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?MnxBXpakAOuvHCXnuWDZY3TAkR6Y9dlhD12h3Z86qca/YwM8Lrpze1amnCTQ?=
 =?us-ascii?Q?mFnSivTYAt0Sbob44S2nPKTtvjQAazKlZYUX8REYyJ+ez+oY6OEsWhuZfNHb?=
 =?us-ascii?Q?0MPzQWqDG5lWZpb4QDXo9alJ/BgtRnG58EpYYAe/KfLX3tfa/RyBWpx0i9kK?=
 =?us-ascii?Q?jLvpLTXpVOUuQ8V12X5w8Tqbn9lwyWlGOZxfv8QHo7X1X1At/VdCzZIpNKjy?=
 =?us-ascii?Q?mAhHHY3hn+RCttfoVM+LjjNtk9LuR1SBzLf7XjykJUO758ru+ggYDHp+624/?=
 =?us-ascii?Q?Qi+xkpu4m+jK2+rQG9C3pp6Ep2ulcuwtAPrqfkIzSJv5aiOyH7Z/Bfxmm6Am?=
 =?us-ascii?Q?/1b+k6xSVR3ESobPmPcLuwK6H4vR3nWBezhgn5Ec1a0dqopRkcp6TgvA2Ak6?=
 =?us-ascii?Q?KX4zTROJiFGWdI3L7XM5Eg37Z0d3SBg4dPF4WxlenWmQjUIhkwzSNBFtiJil?=
 =?us-ascii?Q?MN3mps2MGupKZ9txTyjK/eJ7XN32h1Xy2+CXOkz4GsqgLTXQUqBPl+D0CNXk?=
 =?us-ascii?Q?IC8+3XB+4JEnltQKnZ3TN8r8elSo9YGrvWGBwUw91/fSpWevjedjy3L9G6WH?=
 =?us-ascii?Q?Sxli9PcTjhl9W/rDAOTaUB9TqoZ5zM46RwTmajdP3yIY6m+/vCFRWz3B9ldq?=
 =?us-ascii?Q?dtPyiPRuOIIgwZEm2kxa0j8TYM2fM7a0sxQUuzxil/9FUjtwxf4cEJpR+WMa?=
 =?us-ascii?Q?4rZ/TSo0hxEGrznzvaPg3/7pooir5/D158alyQAE15xl6zDTBkvwRxWk1ugk?=
 =?us-ascii?Q?thpXE+yo8WaZTBy8X7854XWPZxzQq+1hxKf7R6ZfbHgzlD2NsB2XMNqHnDu0?=
 =?us-ascii?Q?iT7U5o3/XX+GuPc3zneEr1YbKR7+UIKw5XUBtoQsppC4myMIT0UeR8VVyPPD?=
 =?us-ascii?Q?JfwsUef6zCrZIYJnnMj+nzUt1ceQMsPzSEGjDkO1eC2hSNWdeGBXipplaJYs?=
 =?us-ascii?Q?lq86e7V1KCEckxYYAYTMLcc2iZ0lD8ooA4h4sXLUScofj7pmmPa6q7Xfl8Sa?=
 =?us-ascii?Q?OSNKG0+rbpX7Y/xT9+hh0LhCB8ri8BXb7l7IC//ypfhJ5U0hJRDcEmZ82zJt?=
 =?us-ascii?Q?+U7PBFIM5cnizPbz8n/yCVtBzZE40v/NE4zRaGoXXbbpFKwKAmDD2FbUopuI?=
 =?us-ascii?Q?5kONTivFzY4rRvaCztKZyK4Ph+wFIaruLH10DLC26rWqKsbc4oVZZwI+vHMo?=
 =?us-ascii?Q?v7SyuSJ9ri0DLP8648vMWfIQDMj+RaOTDBLxw+4M3PFRc1RuuAbjrd1IfnZ0?=
 =?us-ascii?Q?2xM7BatIgNPo9AOC1UCpE3oK1ndk8x5mDaDmMHyLvU/eYpodQCw5LTkNXAKw?=
 =?us-ascii?Q?U+4Pr+SQFi9MDqWwNf/g6pUSdMXzNduVEqkDWCtnkaBJy2FdGEQFXR1hy+M2?=
 =?us-ascii?Q?vxGtcdUXdr3ThEibyPpIh/VzRU2ukdNG9ERzehsxIWuy/SfqZRv6ntEftION?=
 =?us-ascii?Q?tvR1yU4K4HRZSmuU9Or+8yq6TlGm9iqj2sWJJF6ZezNx82RgubHFRMgRah0X?=
 =?us-ascii?Q?z3QBFmyay7QY+QmSFiivnJ5FtZ02kqRBmdYTThGh4ScEql/uulMNLgeqD3hc?=
 =?us-ascii?Q?JnD+0jtnMyepueE7qDtcn4ms6W7dYNiyyZ9sZdWK?=
Content-Type: multipart/alternative;
	boundary="_000_DM8PR09MB6677C5B4FDBDFC0F82298DC7B8DE2DM8PR09MB6677namp_"
MIME-Version: 1.0
X-OriginatorOrg: fnal.gov
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DM8PR09MB6677.namprd09.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: a1509b20-2635-4160-2ec6-08dc9c84dce7
X-MS-Exchange-CrossTenant-originalarrivaltime: 04 Jul 2024 23:56:03.5195
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DS0PR09MB10801
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/DM8PR09MB6677C5B4FDBDFC0F82298DC7B8DE2%40DM8PR09MB6677.namprd09.prod.outlook.com>
Precedence: bulk

--_000_DM8PR09MB6677C5B4FDBDFC0F82298DC7B8DE2DM8PR09MB6677namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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_activit=
y where cardinality(pg_blocking_pids(pid)) > 0)
                 and usename =3D 'DBUSER_10'
                 and now() - state_change >=3D interval $a'${TIMEOUT_MINS}'=
$a
                 order by now() - state_change >=3D interval $a'${TIMEOUT_M=
INS}'$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.


--_000_DM8PR09MB6677C5B4FDBDFC0F82298DC7B8DE2DM8PR09MB6677namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;
	font-weight:normal;
	font-style:normal;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Hello:<o:p></o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Following works-<o:=
p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">&nbsp;&nbsp;&nbsp; =
SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity<o:p></o:p></spa=
n></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">&nbsp;&nbsp;&nbsp; =
WHERE pid IN (select unnest(pg_blocking_pids(pid)) from pg_stat_activity wh=
ere cardinality(pg_blocking_pids(pid)) &gt; 0)<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; and usename =3D 'DBUSER_10'<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; and now() - state_change &gt;=3D interval $a'${TIMEOUT_MINS}'$a<o:p></o:=
p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; order by now() - state_change &gt;=3D interval $a'${TIMEOUT_MINS}'$a des=
c limit 1;<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">How can I rewrite t=
he above in psql and pg_terminate_backend &nbsp;all pids that meet above cr=
iteria (essentially remove limit 1) ?<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Thanks!<o:p></o:p><=
/span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Note:<o:p></o:p></s=
pan></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">I run this in Linux=
. TIMEOUT_MINS is env variable.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
</div>
</body>
</html>

--_000_DM8PR09MB6677C5B4FDBDFC0F82298DC7B8DE2DM8PR09MB6677namp_--