PL/pgSQL Loops don't update current timestamp / now()-CodePudding

I want to see how long a loop iteration takes inside a DO block in postgres. The basic layout is as follows:

DO $$
declare v_time timestamptz;
declare i record;
begin
for i in select generate_series(1, 5) t
loop

select current_timestamp into v_time;

perform pg_sleep(i.t);
-- something done here (pg_sleep to ensure some time passes)

raise notice '%', v_time - (select current_timestamp);
-- expect negative interval from RAISE. 
end loop;
end; $$;

However, when I run this (have tried on Postgres 13 and 9), I get an interval of 0S returned:

NOTICE:  00:00:00
NOTICE:  00:00:00
NOTICE:  00:00:00
NOTICE:  00:00:00
NOTICE:  00:00:00
DO

Query returned successfully in 15 secs 389 msec.

I have done this previously and have never run into this issue before, so I guess my question is "what am I doing wrong this time?" instead of "why is postgres behaving unexpectedly?"

CodePudding user response：

current_timestamp is defined to be:

the start time of the current transaction, their values do not change during the transaction

you probably want to use clock_timestamp() instead which returns a value that changes within a transaction, see the link above for a more complete description.