From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: postgresql statement problem
Date: Thu, 25 Aug 2022 23:46:49 +0200
Message-ID: <2025c17d-3ee7-be3f-9136-9234dbed4d5d@jakobs.com> (raw)
In-Reply-To: <CAGHjuaZvaCs0kP-MwODYi=TtkQzSXWDT2hhj+04+T5He-6dtQw@mail.gmail.com>
References: <CAGHjuab0DACgd0EE8nvAYs7j+EYGTA3a8MtejnjeTM7mO26vcQ@mail.gmail.com>
<f2e6f9bb-e35b-e29c-4204-bc6e0e43fe3d@jakobs.com>
<CAGHjuaZvaCs0kP-MwODYi=TtkQzSXWDT2hhj+04+T5He-6dtQw@mail.gmail.com>
Am 25.08.22 um 22:51 schrieb liam saffioti:
> Hi Holger,
>
> Okay, I added the output with \x option:
>
> 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 | DWH_NODI
> state | idle in transaction
> wait_event | ClientRead
> backend_xmin | 47730267
> xact_start | 2022-08-25 21:03:02.961309
> query_start | 2022-08-25 21:05:32.27796
> state_change | 2022-08-25 21:05:32.285729
> now | 2022-08-25 23:50:34.104552
> -[ RECORD 2 ]+------------------------------
> usename | DWH_NODI
> state | active
> wait_event | ClientWrite
> backend_xmin | 47730267
> xact_start | 2022-08-25 21:03:56.984189
> query_start | 2022-08-25 21:03:57.062329
> state_change | 2022-08-25 21:03:57.062329
> now | 2022-08-25 23:50:34.104552
>
> Holger Jakobs <holger@jakobs.com>, 25 Ağu 2022 Per, 23:39 tarihinde
> şunu yazdı:
>
> 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? The detail is below:
> > (DWH user is not a superuser, it is a standard user that has select
> > only privilege.)
> >
> > show statement_timeout ;
> > statement_timeout
> > -------------------
> > 2h
> >
> > show max_standby_archive_delay ;
> > max_standby_archive_delay
> > ---------------------------
> > 2h
> >
> > show max_standby_streaming_delay ;
> > 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;
> > usename | state | client_addr | wait_event |
> > backend_xmin | xact_start | query_start
> |
> > state_change | now
> >
> --------+-----------------+---------------------+--------------+-------------+--------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
> > DWH | idle in transaction | ClientRead | 47730267 | 2022-08-25
> > 21:03:02.961309+03 | 2022-08-25 21:05:32.27796+03 | 2022-08-25
> > 21:05:32.285729+03 | 2022-08-25 23:22:24.830829
> > DWH | active | ClientWrite | 47730267 | 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.postgresql.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
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: postgresql statement problem
In-Reply-To: <2025c17d-3ee7-be3f-9136-9234dbed4d5d@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox