I create a trigger in Postgresql. This trigger executes some update statements. I want to calculate the time how much time to do these statements. An example I have two update statements and I do not know these statements execution time. The first updates execution time what is it and also second updates execution time what is it? {
create or replace
function public.insert_sth() returns trigger language plpgsql as $function$
declare time1 timestamp without time zone;
declare time2 timestamp without time zone;
declare timediff bigint;
begin
time1:=current_timestamp;--now();
RAISE NOTICE 'Calling update healthy time1 %', time1;
if exists(
select 1
from
basetable
where
id= NEW.id) then update
basetable
set
previous_id= this_id
where
id= NEW.id;
update
basetable
set
this_id= NEW.id
where
id= NEW.id;
update
basetable
set
healthy_id= (
select id
from
sometable
where
connectionresult = 0
or connectionresult = 2
and sometable.id= NEW.id
order by
sometable.actiontime desc
limit 1)
where
id= new.id;
time2:=current_timestamp;--now();
timediff:= (EXTRACT('epoch' from time2) - EXTRACT('epoch' from time1)) * 1000;
RAISE NOTICE 'Calling update healthy check %', timediff;
RAISE NOTICE 'Calling update healthy time2 %', time2;
return new;
else insert
into
bastable(id,
this_id)
values(NEW.id,
NEW.id);
return new;
end if;
end;
$function$ ;
}
But the output is :
00000: Calling update healthy time1 2019-07-18 17:00:08.085
00000: Calling update healthy check 0
00000: Calling update healthy time2 2019-07-18 17:00:08.085
this is impossible. The update execution should not be 0 ms. :)
The problem is with current_timestamp. It is same for the whole transaction.
Use clock_timestamp instead
It will reflect the clock time.