pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql statement problem
4+ messages / 2 participants
[nested] [flat]
* postgresql statement problem
@ 2022-08-25 20:31 liam saffioti <liam.saffiotti@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: liam saffioti @ 2022-08-25 20:31 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql statement problem
@ 2022-08-25 20:39 Holger Jakobs <holger@jakobs.com>
parent: liam saffioti <liam.saffiotti@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2022-08-25 20:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql statement problem
@ 2022-08-25 20:51 liam saffioti <liam.saffiotti@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 4+ messages in thread
From: liam saffioti @ 2022-08-25 20:51 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
> >
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgresql statement problem
@ 2022-08-25 21:46 Holger Jakobs <holger@jakobs.com>
parent: liam saffioti <liam.saffiotti@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Holger Jakobs @ 2022-08-25 21:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-08-25 21:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-25 20:31 postgresql statement problem liam saffioti <liam.saffiotti@gmail.com>
2022-08-25 20:39 ` Holger Jakobs <holger@jakobs.com>
2022-08-25 20:51 ` liam saffioti <liam.saffiotti@gmail.com>
2022-08-25 21:46 ` Holger Jakobs <holger@jakobs.com>
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