What if its just SLEEP for 1 second. Why would it keep my stored procedure hanging ?
On 10/10/07, Michal Taborsky - Internet Mall <[EMAIL PROTECTED]> 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> >
