Re: SLEEP in posgresql

What if its just SLEEP for 1 second. Why would it keep my stored procedure
hanging ?

On 10/10/07, Michal Taborsky - Internet Mall <michal(dot)taborsky(at)mall(dot)cz>
wrote:
>
> Jasbinder Singh Bali napsal(a):
> > Instead of that, I re-engineered my while loop in the stored procedure
> > as follows.
> ...
> > I was wondering how to implement the SLEEP functionality here.
>
> Hello.
>
> I can't comment the function itself, but I want to bring something else
> to your attention. Note, that the stored procedure is always run as a
> single transaction and by doing the sleep in it, it will probbly run for
> a long time, or maybe even forever. The problem is that "Long running
> transactions are evil(tm)"
>
> Postgres, and almost any real database engine for that matter, has
> problems when there are tansactions that run for a very long time. It
> prevents the cleanup of stale records, because the engine has to keep
> them around for this long running transaction.
>
> You might consider doing the actual work in the transaction, but the
> sleeping in between shoud be done outside.
>
> Note to PG developers:
> Is there any thought being given to have the PL/pgSQL scripting language
> outside the function body? Like Ora has? It would be perfect for this
> case and I remember more than a dozen times in last year when I could
> have used it and saved some PHP work (and network communiaction).
>
> --
> Michal Táborský
> chief systems architect
> Internet Mall, a.s.
> <http://www.MALL.cz>
>