Обсуждение: pg_sleep() inside plpgsql block - pro & cons
Hi! 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? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Hi: On Tue, Oct 2, 2018 at 12:10 PM, pinker <pinker@onet.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.
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... You know it's always time to do refactoring and in this case it's a lot of time because of the amount and complexity of the logic in db. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Tue, Oct 2, 2018 at 3:44 PM, pinker <pinker@onet.eu> wrote: ... > 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... Well, not knowing the logic I cannot be sure, but you can try the "it depends on isolation level" I pointed to. > You know it's always time to do refactoring and in this case it's a lot of > time because of the amount and complexity of the logic in db. Not having seen it, I trust your word. But I doubt the dessign can be correct, it seems a case of "all I know is pl/xx, so I'll do everything I can in it. I got one product with a similar problem in Oracle. But unless the logic is really perverse, it seems to me you could do two procs, the normal one ( without the pg_sleep in a loop ), and a wrapping one testing for the "data appears" condition and calling the first, and call the second in a loop. But again, details will be needed and trusting your words I do not want them on the list ;-> . Francisco Olarte.
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