Re: Avoiding roundoff error in pg_sleep()

Hi
 
I suspect, that any user, that run something like pg_sleep(1000000000), 
start transaction, that stops autovacuum and creates other negative effects up to server crash,
and only this user can stop it by command interrupt (all signals only restart 
this sleep or kill whole server).
If I am correct, why Postgres needs this command?
 
Best regard,
Vladlen Popolitov.

On Thursday, September 25, 2025 21:42 MSK, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

 
I chanced to notice that if you ask pg_sleep for 1ms delay,
what you actually get is 2ms, for example

regression=# \timing on
Timing is on.
regression=# do $$ begin
for i in 1..1000 loop
perform pg_sleep(0.001);
end loop; end $$;
DO
Time: 2081.175 ms (00:02.081)
regression=# do $$ begin
for i in 1..1000 loop
perform pg_sleep(0.002);
end loop; end $$;
DO
Time: 2177.407 ms (00:02.177)

A bit of gdb-ing confirms that the delay passed to WaitLatch() is 2ms,
so the problem is fundamentally one of floating-point roundoff error
in pg_sleep's calculation of delay_ms. I didn't try to figure out
exactly why that's happening. It may well vary depending on the
absolute magnitude of current values of GetCurrentTimestamp(),
because I don't recall having noticed any such behavior back when
this code was written.

Anyway, I propose trying to get rid of this misbehavior by avoiding
floating point in the delay computation, as attached. With this
patch I get less surprising behavior:

regression=# \timing on
Timing is on.
regression=# do $$ begin
for i in 1..1000 loop
perform pg_sleep(0.001);
end loop; end $$;
DO
Time: 1063.997 ms (00:01.064)
regression=# do $$ begin
for i in 1..1000 loop
perform pg_sleep(0.002);
end loop; end $$;
DO
Time: 2172.849 ms (00:02.173)

The code is a little more tied to TimestampTz being measured in
microseconds than it was before, but it wouldn't really be much
harder to fix if we ever change that.

regards, tom lane

-- 
 Best regards,

Vladlen Popolitov.