Re: Avoiding roundoff error in pg_sleep()

Hi Tom,

Tom Lane писал(а) 2025-09-25 22:36:
> =?utf-8?q?=D0=9F=D0=BE=D0=BF=D0=BE=D0=BB=D0=B8=D1=82=D0=BE=D0=B2_=D0=92=D0=BB=D0=B0=D0=B4=D0=BB=D0=B5=D0=BD?=
> <v(dot)popolitov(at)postgrespro(dot)ru> writes:
>> 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).
>
> How is this different from any other long-running query?
> If you'd bothered to test, you'd have seen that pg_sleep()
> is interruptible by query cancel, just like anything else,
> so "kill whole server" is not required.

I will try to explain my worries about this. Long running queries could
run long
due to higher input-output, and they are limited at least by work_mem
parameter.
pg_sleep() is not limited by anything.

> We're not in the business of trying to limit users' resource
> consumption. If we wanted to do that, it'd be a very major
> undertaking.

For example, mssql does not have function like this, it has
command WAIT FOR interval . This interval cannot be more than 24H.
And this separate command does not start transaction. It is
possible to include it in transaction block anyway, but if
user needs pause, he get it without disturbing of other users.

In our case the user gets a pause and stops autovacuum, create index
concurrently and other commands relaying to transaction id
for all other users - without choice to avoid this behaviour.

I heard about customer, who had real trouble with this. I can
understand them, because in yearly 1990s I stopped university
mainframe by simple command "DISPLAY MEMORY" asking for all memory,
and later explained to administrator - "I just wanted to see,
what will happen".
It looks like sleep has the wrong place in a function. It should be
an utility command, that does not start a transaction.

Best regards,

Vladlen Popolitov.