postgresqlnode-pg-pool

Why does UPDATE not set a later end time?


node-pg

await db.query(
`DO $$ 
 DECLARE 
     pbid INT;
 BEGIN 
     INSERT INTO public.bp(process_id) SELECT ${ID} RETURNING id INTO pbid;
     COMMIT;
     REFRESH MATERIALIZED VIEW public.vm WITH DATA;
     UPDATE public.bp SET under_process = FALSE, ended_at = now() WHERE id = pbid;
 END;$$;`
);

This script executes fine but ended_at is always the same time as start time.
The refresh takes around 2 min.

P.S.: I have a trigger currently disabled that does new.ended_at := now();

At least tell me why this is happening.


Solution

  • See the manual, NOW() returns the start time of the current transaction, their values do not change during the transaction.

    While clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command

    Use clock_timestamp() to solve your issue.