Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRKgi-0006of-3h for pgsql-admin@arkaria.postgresql.org; Thu, 25 Aug 2022 21:46:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oRKgg-0000UZ-I5 for pgsql-admin@arkaria.postgresql.org; Thu, 25 Aug 2022 21:46:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRKgg-0000UQ-3q for pgsql-admin@lists.postgresql.org; Thu, 25 Aug 2022 21:46:54 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRKgd-0003cz-DD for pgsql-admin@lists.postgresql.org; Thu, 25 Aug 2022 21:46:53 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 4BFD5380A8B; Thu, 25 Aug 2022 23:46:50 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id uou0fQVNf7Gd; Thu, 25 Aug 2022 23:46:50 +0200 (CEST) Received: from [192.168.178.86] (p5b249d86.dip0.t-ipconnect.de [91.36.157.134]) by rs.plausibolo.de (Postfix) with ESMTPSA id EAF5D380876; Thu, 25 Aug 2022 23:46:49 +0200 (CEST) Message-ID: <2025c17d-3ee7-be3f-9136-9234dbed4d5d@jakobs.com> Date: Thu, 25 Aug 2022 23:46:49 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.1.2 To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Subject: Re: postgresql statement problem In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------PMqSXsa50Dck00IEmPBGkEfk" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------PMqSXsa50Dck00IEmPBGkEfk Content-Type: multipart/mixed; boundary="------------WlQOXJZuPYP6SMFjISfIijEA"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <2025c17d-3ee7-be3f-9136-9234dbed4d5d@jakobs.com> Subject: Re: postgresql statement problem References: In-Reply-To: --------------WlQOXJZuPYP6SMFjISfIijEA Content-Type: multipart/alternative; boundary="------------Rc1ekn5vNVBvUakgoHpUVp60" --------------Rc1ekn5vNVBvUakgoHpUVp60 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMjUuMDguMjIgdW0gMjI6NTEgc2NocmllYiBsaWFtIHNhZmZpb3RpOg0KPiBIaSBIb2xn ZXIsDQo+DQo+IE9rYXksIEkgYWRkZWQgdGhlIG91dHB1dCB3aXRoIFx4IG9wdGlvbjoNCj4N Cj4gc2hvdyBzdGF0ZW1lbnRfdGltZW91dCA7DQo+IC1bIFJFQ09SRCAxIF0tLS0tLSstLS0N Cj4gc3RhdGVtZW50X3RpbWVvdXQgfCAyaA0KPg0KPiBzaG93IG1heF9zdGFuZGJ5X2FyY2hp dmVfZGVsYXkgOw0KPiAtWyBSRUNPUkQgMSBdLS0tLS0tLS0tLS0tLSstLS0NCj4gbWF4X3N0 YW5kYnlfYXJjaGl2ZV9kZWxheSB8IDJoDQo+DQo+IHNob3cgbWF4X3N0YW5kYnlfc3RyZWFt aW5nX2RlbGF5IDsNCj4gLVsgUkVDT1JEIDEgXS0tLS0tLS0tLS0tLS0tLSstLS0NCj4gbWF4 X3N0YW5kYnlfc3RyZWFtaW5nX2RlbGF5IHwgMmgNCj4NCj4gU0VMRUNUIHVzZW5hbWUsIHN0 YXRlLHdhaXRfZXZlbnQsIA0KPiBiYWNrZW5kX3htaW4seGFjdF9zdGFydCxxdWVyeV9zdGFy dCxzdGF0ZV9jaGFuZ2Usbm93KCkgRlJPTSANCj4gcGdfc3RhdF9hY3Rpdml0eSBXSEVSRSBi YWNrZW5kX3htaW4gSVMgTk9UIE5VTEwgT1JERVIgQlkgDQo+IGFnZShiYWNrZW5kX3htaW4p IERFU0M7DQo+IC1bIFJFQ09SRCAxIF0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t DQo+IHVzZW5hbWUgwqAgwqAgwqB8IERXSF9OT0RJDQo+IHN0YXRlIMKgIMKgIMKgIMKgfCBp ZGxlIGluIHRyYW5zYWN0aW9uDQo+IHdhaXRfZXZlbnQgwqAgfCBDbGllbnRSZWFkDQo+IGJh Y2tlbmRfeG1pbiB8IDQ3NzMwMjY3DQo+IHhhY3Rfc3RhcnQgwqAgfCAyMDIyLTA4LTI1IDIx OjAzOjAyLjk2MTMwOQ0KPiBxdWVyeV9zdGFydCDCoHwgMjAyMi0wOC0yNSAyMTowNTozMi4y Nzc5Ng0KPiBzdGF0ZV9jaGFuZ2UgfCAyMDIyLTA4LTI1IDIxOjA1OjMyLjI4NTcyOQ0KPiBu b3cgwqAgwqAgwqAgwqAgwqB8IDIwMjItMDgtMjUgMjM6NTA6MzQuMTA0NTUyDQo+IC1bIFJF Q09SRCAyIF0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQo+IHVzZW5hbWUgwqAg wqAgwqB8IERXSF9OT0RJDQo+IHN0YXRlIMKgIMKgIMKgIMKgfCBhY3RpdmUNCj4gd2FpdF9l dmVudCDCoCB8IENsaWVudFdyaXRlDQo+IGJhY2tlbmRfeG1pbiB8IDQ3NzMwMjY3DQo+IHhh Y3Rfc3RhcnQgwqAgfCAyMDIyLTA4LTI1IDIxOjAzOjU2Ljk4NDE4OQ0KPiBxdWVyeV9zdGFy dCDCoHwgMjAyMi0wOC0yNSAyMTowMzo1Ny4wNjIzMjkNCj4gc3RhdGVfY2hhbmdlIHwgMjAy Mi0wOC0yNSAyMTowMzo1Ny4wNjIzMjkNCj4gbm93IMKgIMKgIMKgIMKgIMKgfCAyMDIyLTA4 LTI1IDIzOjUwOjM0LjEwNDU1Mg0KPg0KPiBIb2xnZXIgSmFrb2JzIDxob2xnZXJAamFrb2Jz LmNvbT4sIDI1IEHEn3UgMjAyMiBQZXIsIDIzOjM5IHRhcmloaW5kZSANCj4gxZ91bnUgeWF6 ZMSxOg0KPg0KPiAgICAgQW0gMjUuMDguMjIgdW0gMjI6MzEgc2NocmllYiBsaWFtIHNhZmZp b3RpOg0KPiAgICAgPiBIZWxsbyBUZWFtLA0KPiAgICAgPg0KPiAgICAgPiBJIGFtIHVzaW5n IFBHIDE0LjUsIGFuZCBJIGhhdmUgYSBwcm9ibGVtIHdpdGggY29ubmVjdGlvbi4gQQ0KPiAg ICAgY29ubmVjdGlvbg0KPiAgICAgPiB3b3JrcyBtb3JlIHRoYW4gMiBob3VycyBldmVuIHRo b3VnaCBpdCdzIGxpbWl0ZWQgdG8gdHdvIGhvdXJzLg0KPiAgICAgSG93IGlzDQo+ICAgICA+ IHRoaXMgcG9zc2libGU/wqAgVGhlIGRldGFpbCBpcyBiZWxvdzoNCj4gICAgID4gKERXSCB1 c2VyIGlzIG5vdCBhIHN1cGVydXNlciwgaXQgaXMgYSBzdGFuZGFyZMKgdXNlciB0aGF0IGhh cyBzZWxlY3QNCj4gICAgID4gb25seSBwcml2aWxlZ2UuKQ0KPiAgICAgPg0KPiAgICAgPiBz aG93IHN0YXRlbWVudF90aW1lb3V0IDsNCj4gICAgID4gwqBzdGF0ZW1lbnRfdGltZW91dA0K PiAgICAgPiAtLS0tLS0tLS0tLS0tLS0tLS0tDQo+ICAgICA+IMKgMmgNCj4gICAgID4NCj4g ICAgID4gc2hvdyBtYXhfc3RhbmRieV9hcmNoaXZlX2RlbGF5IDsNCj4gICAgID4gwqBtYXhf c3RhbmRieV9hcmNoaXZlX2RlbGF5DQo+ICAgICA+IC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLQ0KPiAgICAgPiDCoDJoDQo+ICAgICA+DQo+ICAgICA+IHNob3cgbWF4X3N0YW5kYnlf c3RyZWFtaW5nX2RlbGF5IDsNCj4gICAgID4gwqBtYXhfc3RhbmRieV9zdHJlYW1pbmdfZGVs YXkNCj4gICAgID4gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCj4gICAgID4gwqAy aA0KPiAgICAgPg0KPiAgICAgPiBTRUxFQ1QgdXNlbmFtZSwgc3RhdGUsd2FpdF9ldmVudCwN Cj4gICAgID4gYmFja2VuZF94bWluLHhhY3Rfc3RhcnQscXVlcnlfc3RhcnQsc3RhdGVfY2hh bmdlLG5vdygpIEZST00NCj4gICAgID4gcGdfc3RhdF9hY3Rpdml0eSBXSEVSRSBiYWNrZW5k X3htaW4gSVMgTk9UIE5VTEwgT1JERVIgQlkNCj4gICAgID4gYWdlKGJhY2tlbmRfeG1pbikg REVTQzsNCj4gICAgID4gwqB1c2VuYW1lIMKgIMKgIHwgwqAgwqAgwqAgwqBzdGF0ZSDCoCDC oCDCoCDCoHwgY2xpZW50X2FkZHIgwqB8IHdhaXRfZXZlbnQgwqB8DQo+ICAgICA+IGJhY2tl bmRfeG1pbiB8IMKgIMKgIMKgIMKgIMKgeGFjdF9zdGFydCDCoCDCoCDCoCDCoCDCoCB8IMKg cXVlcnlfc3RhcnQgwqAgwqANCj4gICAgIMKgIMKgIMKgfA0KPiAgICAgPiDCoCDCoCDCoCDC oCBzdGF0ZV9jaGFuZ2UgwqAgwqB8IG5vdw0KPiAgICAgPg0KPiAgICAgLS0tLS0tLS0rLS0t LS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tKy0t LS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KPiAgICAg PiBEV0jCoCDCoHwgaWRsZSBpbiB0cmFuc2FjdGlvbiDCoHwgQ2xpZW50UmVhZCDCoHwgwqA0 NzczMDI2NyB8IDIwMjItMDgtMjUNCj4gICAgID4gMjE6MDM6MDIuOTYxMzA5KzAzIHwgMjAy Mi0wOC0yNSAyMTowNTozMi4yNzc5NiswMyDCoHwgMjAyMi0wOC0yNQ0KPiAgICAgPiAyMTow NTozMi4yODU3MjkrMDMgfCAyMDIyLTA4LTI1IDIzOjIyOjI0LjgzMDgyOQ0KPiAgICAgPiBE V0jCoCDCoHwgYWN0aXZlIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgQ2xpZW50V3JpdGUgfCDC oDQ3NzMwMjY3IHwgMjAyMi0wOC0yNQ0KPiAgICAgPiAyMTowMzo1Ni45ODQxODkrMDMgfCAy MDIyLTA4LTI1IDIxOjAzOjU3LjA2MjMyOSswMyB8IDIwMjItMDgtMjUNCj4gICAgID4gMjE6 MDM6NTcuMDYyMzI5KzAzIHwgMjAyMi0wOC0yNSAyMzoyMjoyNC44MzA4MjkNCj4NCj4gICAg IFlvdSBoYXZlIHNldCBhIHN0YXRlbWVudCB0aW1lb3V0LCBidXQgY29tcGxhaW4gdGhhdCBh IGNvbm5lY3Rpb24NCj4gICAgIChzZXNzaW9uPykgdGFrZXMgbG9uZ2VyLA0KPg0KPiAgICAg VW5mb3J0dW5hdGVseSwgdGhlIHN0YXRlbWVudCBhbmQgdGhlIHRhYmxlIHlvdSBwb3N0ZWQg ZG9uJ3QgbWF0Y2guDQo+ICAgICBQbHVzLA0KPiAgICAgdGhlIHRhYmxlIGlzIHZlcnkgd2lk ZSBhbmQgYWxtb3N0IGlsbGVnaWJsZS4NCj4NCj4gICAgIFBsZWFzZSB1c2Ugb3B0aW9uIFx4 IG9mIHBzcWwgdG8gcHJvZHVjZSBhIGJldHRlciBvdXRwdXQuDQo+DQo+ICAgICBSZWdhcmRz LA0KPg0KPiAgICAgSG9sZ2VyDQo+DQo+DQpUaGF0IGxvb2tzIGJldHRlci4gWWVzLCB0aGUg c3RhdGVtZW50IGlzIGFjdGl2ZSBhZnRlciAwMjo0NjozNy4wNDIyMjMgaW4gDQpzcGl0ZSBv ZiB5b3VyIGxpbWl0IG9mIDIgaG91cnMuDQoNCkl0IGNvdWxkIGJlIGJlY2F1c2UgaXQgaXMg bm90IGFjdHVhbGx5IHJ1bm5pbmcsIGJ1dCB3YWl0aW5nIGZvciB0aGUgDQpjbGllbnQgdG8g cmVjZWl2ZSBkYXRhIChDbGllbnRXcml0ZSksIHNlZSANCmh0dHBzOi8vd3d3LnBvc3RncmVz cWwub3JnL2RvY3MvY3VycmVudC9tb25pdG9yaW5nLXN0YXRzLmh0bWwjV0FJVC1FVkVOVC1D TElFTlQtVEFCTEUNCg0KV2hpbGUgdGhlIHN0YXRlbWVudCBpcyB3YWl0aW5nLCBpdCBjYW5u b3QgbG9vayBhdCB0aGUgdGltZW91dC4gSSB3b3VsZCANCnRoaW5rIHRoYXQgaXQgdGVybWlu YXRlcyBhcyBzb29uIGFzIGl0IGxlYXZlcyB0aGUgd2FpdCBzdGF0ZS4NCg0KUmVnYXJkcywN Cg0KSG9sZ2VyDQoNCg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwg VGVsLiArNDktMTc4LTk3NTkwMTINCg0K --------------Rc1ekn5vNVBvUakgoHpUVp60 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 25.08.22 um 22:51 schrieb liam saffioti:
Hi Holger,

Okay, I added the output with \x option:=C2=A0=C2=A0

show statement_timeout ;
-[ RECORD 1 ]-----+---
statement_timeout | 2h

show max_standby_archive_delay ;
-[ RECORD 1 ]-------------+---
max_standby_archive_delay | 2h

show max_standby_streaming_delay ;
-[ RECORD 1 ]---------------+---
max_standby_streaming_delay | 2h

SELECT usename, state,wait_event, backend_xmin,xact_start,query_start,state_change,now() FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;
-[ RECORD 1 ]+------------------------------
usename =C2=A0 =C2=A0 =C2=A0| DWH_NODI
state =C2=A0 =C2=A0 =C2=A0 =C2=A0| idle in transaction
wait_event =C2=A0 | ClientRead
backend_xmin | 47730267
xact_start =C2=A0 | 2022-08-25 21:03:02.961309
query_start =C2=A0| 2022-08-25 21:05:32.27796
state_change | 2022-08-25 21:05:32.285729
now =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2022-08-25 23:50:34.104= 552
-[ RECORD 2 ]+------------------------------
usename =C2=A0 =C2=A0 =C2=A0| DWH_NODI
state =C2=A0 =C2=A0 =C2=A0 =C2=A0| active
wait_event =C2=A0 | ClientWrite
backend_xmin | 47730267
xact_start =C2=A0 | 2022-08-25 21:03:56.984189
query_start =C2=A0| 2022-08-25 21:03:57.062329
state_change | 2022-08-25 21:03:57.062329
now =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2022-08-25 23:50:34.104= 552

Holger Jakobs <holger@jakobs.com>, 25= A=C4=9Fu 2022 Per, 23:39 tarihinde =C5=9Funu yazd=C4=B1:
= Am 25.08.22 um 22:31 schrieb liam saffioti:
> Hello Team,
>
> I am using PG 14.5, and I have a problem with connection. A connection
> works more than 2 hours even though it's limited to two hours. How is
> this possible?=C2=A0 The detail is below:
> (DWH user is not a superuser, it is a standard=C2=A0user t= hat has select
> only privilege.)
>
> show statement_timeout ;
> =C2=A0statement_timeout
> -------------------
> =C2=A02h
>
> show max_standby_archive_delay ;
> =C2=A0max_standby_archive_delay
> ---------------------------
> =C2=A02h
>
> show max_standby_streaming_delay ;
> =C2=A0max_standby_streaming_delay
> -----------------------------
> =C2=A02h
>
> SELECT usename, state,wait_event,
> backend_xmin,xact_start,query_start,state_change,now() FROM
> pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY
> age(backend_xmin) DESC;
> =C2=A0usename =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0s= tate =C2=A0 =C2=A0 =C2=A0 =C2=A0| client_addr =C2=A0| wait_event =C2=A0|
> backend_xmin | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0xact_star= t =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0query_start =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 state_change =C2=A0 =C2=A0| no= w
> --------+-----------------+---------------------+--------------+---------= ----+--------------+-------------------------------+---------------------= ----------+-------------------------------+------------------------------= -
> DWH=C2=A0 =C2=A0| idle in transaction =C2=A0| ClientRead =C2= =A0| =C2=A047730267 | 2022-08-25
> 21:03:02.961309+03 | 2022-08-25 21:05:32.27796+03 =C2=A0| 2022-08-25
> 21:05:32.285729+03 | 2022-08-25 23:22:24.830829
> DWH=C2=A0 =C2=A0| active =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | ClientWrite | =C2=A047730267 | 2022-08-25
> 21:03:56.984189+03 | 2022-08-25 21:03:57.062329+03 | 2022-08-25
> 21:03:57.062329+03 | 2022-08-25 23:22:24.830829

You have set a statement timeout, but complain that a connection
(session?) takes longer,

Unfortunately, the statement and the table you posted don't match. Plus,
the table is very wide and almost illegible.

Please use option \x of psql to produce a better output.

Regards,

Holger


That looks better. Yes, the statement is active after 02:46:37.042223 in spite of your limit of 2 hours.

It could be because it is not actually running, but waiting for the client to receive data (ClientWrite), see https://www.post= gresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-CLIENT-TABLE=

While the statement is waiting, it cannot look at the timeout. I would think that it terminates as soon as it leaves the wait state.

Regards,

Holger


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------Rc1ekn5vNVBvUakgoHpUVp60-- --------------WlQOXJZuPYP6SMFjISfIijEA-- --------------PMqSXsa50Dck00IEmPBGkEfk Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYwftyQUDAAAAAAAKCRCKV5yTsxlhujXv AJoCahnhbN1egtNpCYWx/CNPllw1lQCfYdGB6crO2PPlJW1HHbdTtfM5Fbk= =4LB8 -----END PGP SIGNATURE----- --------------PMqSXsa50Dck00IEmPBGkEfk--