current_timestamp in a PL/pgSQL function always returns the same value-CodePudding

I admit that after working more than 20 years with Oracle databases I find it hard not to think that way. Our new application uses now a PostgreSQL 12.8 instance and I need to write some SQL scripts that will display some progress information as the script is executing.

Say I have this script:

DO $$
DECLARE
    counter INTEGER;
BEGIN
    RAISE NOTICE '% -- started counting', current_timestamp;
    SELECT count(*) FROM my_table INTO counter;
    perform pg_sleep(10);
    RAISE NOTICE '% -- % is the counting result', current_timestamp, counter;
END;
$$

When running that I am getting the output below:

2022-11-22 02:04:38.339825 00 -- started counting
2022-11-22 02:04:38.339825 00 -- 71690950 is the counting result

Initially the perform pg_sleep(10) was not in my code but I put it when I seen the same time being displayed in my output both before and after counting the number of records. The counting itself takes a few good seconds so not sure what I am doing wrong or is just my understanding about how I should use this script totally wrong?

Thank you in advance.

CodePudding user response：

The documentation is quite clear here:

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction

A PL/pgSQL function always runs in a single transaction, so current_timestamp will always return the same value. Think of it as “transaction timestamp”.

To get the wall time, use clock_timestamp().