plpgsql - How can I control a PostgreSQL function is running in a long period of time -

a program developed using postgresql. program running plpgsql function taking long time(hours or days). want sure function running during long time.

how can know that? don't want use "raise notice" in loop in function because extend running time.

you can see if it's running examining pg_stat_activity process. however, won't tell if function progressing.

you can check see whether backend blocked on locks joining pg_stat_activity against pg_locks see if there open (granted = false) locks table. again, won't tell if it's progressing, if isn't it's not stuck on lock.

if want monitor function's progress need emit log messages or use 1 of other hacks monitoring progress. can (ab)use notify payload listen progress messages. alternately, create sequence call nextval on each time process item in procedure; can select * the_sequence_name; in transaction see approximate progress.

in general i'd recommend setting client_min_messages notice or above raise log record messages appear in logs, without being sent client. reduce overhead, keep counter , log every 100 or 1000 or whatever iterations of loop log occasionally. there's cost updating counter, sure, it's pretty low compared cost of big, slow pl/pgsql procedure this.