pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17537: Dynamically updating statement_timeout not affect the insert query right now.
3+ messages / 3 participants
[nested] [flat]
* BUG #17537: Dynamically updating statement_timeout not affect the insert query right now.
@ 2022-07-03 05:24 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2022-07-03 05:24 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: teng_wang13@163.com
The following bug has been logged on the website:
Bug reference: 17537
Logged by: Parachute Parachute
Email address: teng_wang13@163.com
PostgreSQL version: 14.2
Operating system: Ubuntu 18.04
Description:
As per the manual,
statement_timeout (integer)
Abort any statement that takes more than the specified amount of time. A
value of zero (the default) disables the timeout. The timeout is measured
from the time a command arrives at the server until it is completed by the
server.
https://www.postgresql.org/docs/14/runtime-config-client.html
However, dynamically updating statement_timeout did not affect the insert
query right now.
I start up Postgres with statement_timeout set 0, which means disabling the
timeout. Then I insert 100000000 rows into a table, which finally takes
166498.487ms. During the insert operation, I consider the operation would
takes too much time. So I update statement_timeout to 10s in
postgresql.conf, and "select pg_reload_conf();" in another session to reload
configuration files.
I saw the logs showing the parameter was updated:
2022-07-03 [4229] LOG: received SIGHUP, reloading configuration files
2022-07-03 [4229] LOG: parameter "statement_timeout" changed to "1000"
However, the insert query did not been cancelled, and finally toke about
166s. I think that updating the timeout parameter should also affect the
operation in progress. Because an operation may sometimes take too long, but
it is not safe to interrupt it abruptly.
The scenario:
postgres=# show statement_timeout;
statement_timeout
-------------------
0
(1 row)
postgres=# insert into tbl_test select
generate_series(1,100000000),md5(random()::text),clock_timestamp();
2022-07-03 03:25:10.470 UTC [4229] LOG: received SIGHUP, reloading
configuration files
2022-07-03 03:25:10.471 UTC [4229] LOG: parameter "statement_timeout"
changed to "1000"
INSERT 0 100000000
Time: 166498.487 ms (02:46.498)
In another session:(to update parameter "statement_timeout")
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show statement_timeout;
statement_timeout
-------------------
10s
(1 row)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17537: Dynamically updating statement_timeout not affect the insert query right now.
@ 2022-07-03 15:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-07-03 15:01 UTC (permalink / raw)
To: teng_wang13@163.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> However, dynamically updating statement_timeout did not affect the insert
> query right now.
> I start up Postgres with statement_timeout set 0, which means disabling the
> timeout. Then I insert 100000000 rows into a table, which finally takes
> 166498.487ms. During the insert operation, I consider the operation would
> takes too much time. So I update statement_timeout to 10s in
> postgresql.conf, and "select pg_reload_conf();" in another session to reload
> configuration files.
This is not a bug. Active sessions absorb new values from postgresql.conf
only when idle, not instantaneously mid-query (and I'm pretty sure that is
documented, though I'm too lazy to search the docs right now). I think
trying to do the latter would break far more scenarios than it would
improve.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17537: Dynamically updating statement_timeout not affect the insert query right now.
@ 2022-07-03 15:09 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2022-07-03 15:09 UTC (permalink / raw)
To: teng_wang13@163.com <teng_wang13@163.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Saturday, July 2, 2022, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17537
> Logged by: Parachute Parachute
> Email address: teng_wang13@163.com
> PostgreSQL version: 14.2
> Operating system: Ubuntu 18.04
> Description:
>
> As per the manual,
> statement_timeout (integer)
> Abort any statement that takes more than the specified amount of time. A
> value of zero (the default) disables the timeout. The timeout is measured
> from the time a command arrives at the server until it is completed by the
> server.
> https://www.postgresql.org/docs/14/runtime-config-client.html
>
> However, dynamically updating statement_timeout did not affect the insert
> query right now.
> I start up Postgres with statement_timeout set 0, which means disabling the
> timeout. Then I insert 100000000 rows into a table, which finally takes
> 166498.487ms. During the insert operation, I consider the operation would
> takes too much time. So I update statement_timeout to 10s in
> postgresql.conf, and "select pg_reload_conf();" in another session to
> reload
> configuration files.
>
> I saw the logs showing the parameter was updated:
> 2022-07-03 [4229] LOG: received SIGHUP, reloading configuration files
> 2022-07-03 [4229] LOG: parameter "statement_timeout" changed to "1000"
>
> However, the insert query did not been cancelled, and finally toke about
> 166s. I think that updating the timeout parameter should also affect the
> operation in progress. Because an operation may sometimes take too long,
> but
> it is not safe to interrupt it abruptly.
>
Not a bug.
Per the documentation:
“The configuration file is reread whenever the main server process receives
a SIGHUP signal; this signal is most easily sent by running pg_ctl reload from
the command line or by calling the SQL function pg_reload_conf(). The main
server process also propagates this signal to all currently running server
processes, so that existing sessions also adopt the new values (this will
happen after they complete any currently-executing client command).”
Note the parenthetical.
If you want to cancel a running query you can do so directly
(pg_cancel_backend function) changing a server default to do so makes no
sense.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-07-03 15:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-03 05:24 BUG #17537: Dynamically updating statement_timeout not affect the insert query right now. PG Bug reporting form <noreply@postgresql.org>
2022-07-03 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-07-03 15:09 ` David G. Johnston <david.g.johnston@gmail.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