On 10/10/07, Michal Taborsky - Internet Mall <
michal.taborsky@xxxxxxx> 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>
