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.
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.