I still believe that storing execution start time is the wrong thing
to do as it will miss all long-running statements. Consider this
timeline:
09:55 get all stats changed since 09:50
-> doesn't see the statement because it hasn't started yet
09:57 start long-running statement
10:00 get all stats changed since 09:55
-> doesn't see the statement because it's still running
10:02 long-running statement terminates, storing 09:57 as timestamp
10:05 get all stats changed since 10:00
-> doesn't see the statement because it's too old
Thanks for sharing this. I spent a sleepless night and it seems I found
the solution for the issue.
If we do
WHERE last_execution_start + max_exec_time * INTERVAL '1 ms' > NOW() -
polling_interval
we will grab all long-running statements. The worst thing that might
happen, if some query
has significant run time deviation, then we could grab it several times.
But again this is far
better than re-fetching thousands of rows every time.
For short queries it will just work as expected. "Issues" might be only
with long queries with
a high deviation longer than polling interval. But fetching one or two
rows once in a while is not
a problem.
