perl - Random Lookup Methodology -

i have postgres database table contains rows want @ pseudorandom intervals. want once hour, once day, , once week. lookups @ pseudorandom intervals inside time window. so, want once day should happen @ different time each time runs.

i suspect there easier way this, here's rough plan have: have settings column each lookup item. when script starts, randomizes epoch time each lookup , sets in settings column, identifying time next lookup. run continuous loop wait 1 see if epoch time matches of requested lookups. upon running lookup, recalculate when next lookup should be.

my questions: in design phase, looks it's going duct tape , twine routine. what's right way this?

if chance, idea right way this, idea of repeating loop wait 1 right way go? if had 2 lookups back, there's chance miss 1 can live that.

thanks help!

add column table nextchecktime. use either timestamp or integer raw epoch time. add (non-unique) index on nextchecktime.

when add row database, populate nextchecktime taking current time, adding base interval, , adding/subtracting random factor (maybe 25% of base interval, or whatever appropriate situation). example:

my $interval   = 3600; # 1 hour in seconds $next_check = time + int($interval * (0.75 + rand 0.5)); 

then in loop, select * table order nextchecktime limit 1. sleep until nextchecktime returned (assuming it's not in past), perform lookup, , update nextchecktime described above.

if need handle rows newly added other process, might put limit on sleep. if nextchecktime more 10 minutes in future, sleep 10 minutes , repeat select see if new rows have been added. (again, exact limit depends on situation.)