Re: postgresql statement problem

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
>
> >
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>