Re: pg_sleep() inside plpgsql block - pro & cons

Hi:

On Tue, Oct 2, 2018 at 12:10 PM, pinker <pinker(at)onet(dot)eu> wrote:
> There is second time I see that somebody uses pg_sleep function inside
> plpgsql block. This case is quite similar to the last one - it's some kind
> of wait for data to be loaded. After pg_sleep there is a check if some
> condition is true, if not procedure goes to sleep again. As a result an
> average duration of this function is 1,5h...
> I'm trying to gather pros and cons regarding using pg_sleep this way. What's
> coming to my mind are only 2 cons:
> * clog contention
> * long running open transactions (which is quite good described in here:
> https://www.simononsoftware.com/are-long-running-transactions-bad/)
> So maybe you'll add some more to the list?

With so few details, nothing much can be said. Cons, if the proc is
something like do-stuff wait for data to appear, do more stuff, I
think the function will also need read-commited or something similar
to see the data appear, and fail under serializable. Pattern certainly
smells funny. I do some similar things, but I sleep outside of the
database, is there a reason this can not be done?

Francisco Olarte.