Re: postgresql statement problem

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(at)jakobs(dot)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