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

pinker wrote:
> Francisco Olarte wrote
> > I do some similar things, but I sleep outside of the
> > database, is there a reason this can not be done?
> >
> > Francisco Olarte.
>
> Yes, I do try to convince them to do it outside the db, that's the reason
> I'm looking for some support here :) I'm not sure those 2 reasons are enough
> to convince them, wanted to be prepared...

The main reason why longer sleeping in the database is harmful
is that it causes long database transactions. Remember that a
function always runs inside a database transaction.

Long transactions have two very bad consequences:

- They hold locks for a long time, blocking concurrent transactions
and increasing the likelihood of deadlocks (the word "deadlock"
often works wonders when convincing people).

- They keep autovacuum from freeing dead tuples, which can lead to
bloated tables and indexes if you have many concurrent data
modifications. This wastes storage space and slows down
sequential scans.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com