Message-ID: <961aa7e4-b076-c66b-3c23-32bdd5e352a3@jakobs.com>
Date: Wed, 19 Apr 2023 22:48:18 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.10.0
Subject: Re: pg_cancel_backend and pg_terminate_backend
Content-Language: de-DE
To: pgsql-admin@lists.postgresql.org
References: 
 <CAOC+FBVZFVSF1ntpf7UwQ==YA1KaerSrH0_BH+pmczEp88r2Ug@mail.gmail.com>
 <c43689c6-1aa3-9258-09a7-de8046699dc5@gmx.net>
 <CAOC+FBW=KK04qkY6r0ueEX6JnKK4-8u365uuOrf3UM1UrPNbxA@mail.gmail.com>
From: Holger Jakobs <holger@jakobs.com>
In-Reply-To: 
 <CAOC+FBW=KK04qkY6r0ueEX6JnKK4-8u365uuOrf3UM1UrPNbxA@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------pRNmstBk6itl0hGqidRoV8zi"
Archived-At: 
 <https://www.postgresql.org/message-id/961aa7e4-b076-c66b-3c23-32bdd5e352a3%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------pRNmstBk6itl0hGqidRoV8zi
Content-Type: multipart/mixed; boundary="------------TaWAYmz80lrn5nhuPcBOTjyH";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <961aa7e4-b076-c66b-3c23-32bdd5e352a3@jakobs.com>
Subject: Re: pg_cancel_backend and pg_terminate_backend
References: <CAOC+FBVZFVSF1ntpf7UwQ==YA1KaerSrH0_BH+pmczEp88r2Ug@mail.gmail.com>
 <c43689c6-1aa3-9258-09a7-de8046699dc5@gmx.net>
 <CAOC+FBW=KK04qkY6r0ueEX6JnKK4-8u365uuOrf3UM1UrPNbxA@mail.gmail.com>
In-Reply-To: <CAOC+FBW=KK04qkY6r0ueEX6JnKK4-8u365uuOrf3UM1UrPNbxA@mail.gmail.com>

--------------TaWAYmz80lrn5nhuPcBOTjyH
Content-Type: multipart/alternative;
 boundary="------------7JfQdfS8OOEKn2vQoUcl579R"

--------------7JfQdfS8OOEKn2vQoUcl579R
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64

QW0gMTkuMDQuMjMgdW0gMjI6MTMgc2NocmllYiBXZWxscyBPbGl2ZXI6DQo+IEJlY2F1c2Ug
bG9uZ2VyIGlkbGUgdHJhbnNhY3Rpb25zIGFyZSB1c2VkIGJ5IGEgYnVuY2ggb2YgcHJvY2Vz
c2VzLCBidXQgDQo+IGl0J3MgYSBjZXJ0YWluIGdyb3VwIG9mIHVzZXJzIEkgZG9uJ3Qgd2Fu
dCBoYXZpbmcgaWRsZSB0cmFuc2FjdGlvbnMsIA0KPiBzbyBoZXJlIHdlIGFyZS4uLg0KPg0K
PiBPbiBXZWQsIEFwciAxOSwgMjAyMyBhdCAxOjEx4oCvUE0gVGhvbWFzIEtlbGxlcmVyIDxz
aGFtbWF0QGdteC5uZXQ+IHdyb3RlOg0KPg0KPiAgICAgV2VsbHMgT2xpdmVyIHNjaHJpZWIg
YW0gMTkuMDQuMjAyMyB1bSAyMTo0MToNCj4gICAgID4gSSB1c3VhbGx5IHByZWZlciBwZ19j
YW5jZWxfYmFja2VuZCBiZWNhdXNlIGl0IHNlZW1zLi4gbmljZXIsIGJ1dA0KPiAgICAgPiBs
YXRlbHkgSSd2ZSBoYWQgYSB0cm91Ymxlc29tZSB1c2VyIHdobyBsZWF2ZXMgdHJhbnNhY3Rp
b25zIG9wZW4gYW5kDQo+ICAgICA+IEkndmUgc2NyaXB0ZWQgdXAgYSBjYWxsIHRvIHBnX3Rl
cm1pbmF0ZV9iYWNrZW5kIGFmdGVyIDYwIG1pbnV0ZXMgaW4NCj4gICAgID4gYW4gaWRsZSB0
cmFuc2FjdGlvbi4gSXQgd29ya3Mgd2VsbC4NCj4NCj4gICAgIFdoeSBkb24ndCB5b3UgdXNl
IHRoZSBpZGxlX2luX3RyYW5zYWN0aW9uX3Nlc3Npb25fdGltZW91dCB0byBkbw0KPiAgICAg
dGhpcyBhdXRvbWF0aWNhbGx5Pw0KPg0KQWN0dWFsbHksIHlvdSBjYW4gc2V0IHRoaXMgZm9y
IGNlcnRhaW4gdXNlcnM6DQoNCkFMVEVSIFJPTEUgYWxpY2UgU0VUIGlkbGVfaW5fdHJhbnNh
Y3Rpb25fc2Vzc2lvbl90aW1lb3V0IFRPICcxMDAgcyc7DQoNCkJ1dCB0aGUgZmFjdCB0aGF0
IHRoaXMgaXMgcG9zc2libGUgaW1wbGllcyB0aGF0IGVhY2ggc2Vzc2lvbiBjYW4gc2V0IA0K
dGhpcyBwYXJhbWV0ZXIgdG8gYW55IHZhbHVlLCBBRkFJSy4gQ29ycmVjdCBtZSBpZiBJJ20g
d3JvbmcuDQoNCi0tIA0KDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVs
LiArNDktMTc4LTk3NTkwMTINCg0K
--------------7JfQdfS8OOEKn2vQoUcl579R
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 19.04.23 um 22:13 schrieb Wells
      Oliver:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CAOC+FBW=3DKK04qkY6r0ueEX6JnKK4-8u365uuOrf3UM1UrPNbxA@mail.gm=
ail.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">Because longer idle transactions are used by a
        bunch of processes, but it's a certain group of users I don't
        want having idle transactions, so here we are...</div>
      <br>
      <div class=3D"gmail_quote">
        <div dir=3D"ltr" class=3D"gmail_attr">On Wed, Apr 19, 2023 at
          1:11=E2=80=AFPM Thomas Kellerer &lt;<a href=3D"mailto:shammat@g=
mx.net"
            moz-do-not-send=3D"true" class=3D"moz-txt-link-freetext">sham=
mat@gmx.net</a>&gt;
          wrote:<br>
        </div>
        <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
Wells
          Oliver schrieb am 19.04.2023 um 21:41:<br>
          &gt; I usually prefer pg_cancel_backend because it seems..
          nicer, but<br>
          &gt; lately I've had a troublesome user who leaves
          transactions open and<br>
          &gt; I've scripted up a call to pg_terminate_backend after 60
          minutes in<br>
          &gt; an idle transaction. It works well.<br>
          <br>
          Why don't you use the idle_in_transaction_session_timeout to
          do this automatically?<br>
          <br>
        </blockquote>
      </div>
    </blockquote>
    <p>Actually, you can set this for certain users:</p>
    <p>ALTER ROLE alice SET idle_in_transaction_session_timeout TO '100
      s';</p>
    <p>But the fact that this is possible implies that each session can
      set this parameter to any value, AFAIK. Correct me if I'm wrong.<br=
>
    </p>
    --
    <pre class=3D"moz-signature" cols=3D"72">Holger Jakobs, Bergisch Glad=
bach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------7JfQdfS8OOEKn2vQoUcl579R--

--------------TaWAYmz80lrn5nhuPcBOTjyH--

--------------pRNmstBk6itl0hGqidRoV8zi
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZEBTkgUDAAAAAAAKCRCKV5yTsxlhutDn
AJoDiAp356XaDZGwk2ay2SBVWwsf6wCdGcbVToR7b/27Z/EQUkd+ACW+tuo=
=YRGy
-----END PGP SIGNATURE-----

--------------pRNmstBk6itl0hGqidRoV8zi--