Thread: query timeout
Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated?
Never-mind that. I'm assuming statement_timeout is what I need? On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: > Is there a way to put a timeout on a query so that if it runs > longer than 5 minutes or something it is just automatically > terminated? > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: > Never-mind that. I'm assuming statement_timeout is what I need? Yes, but take care if you change this in postgresql.conf: some queries might reasonaby be expected to take longer than 5 minutes, such as VACUUM. gnari > On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: > > > Is there a way to put a timeout on a query so that if it runs > > longer than 5 minutes or something it is just automatically > > terminated?
Oh that will abort vacuum after that time as well? Can anyone confirm that this is the case? There shouldn't be ANY queries that take that long and if there are then can manually set the parameter when those requests happen. I would prefer to limit by default and allow longer queries only when I specify. But if it kills vacuum I will have to take a different approach. On Mar 3, 2006, at 2:59 AM, Ragnar wrote: > On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: >> Never-mind that. I'm assuming statement_timeout is what I need? > > Yes, but take care if you change this in postgresql.conf: > some queries might reasonaby be expected to take longer > than 5 minutes, such as VACUUM. > > gnari > >> On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: >> >>> Is there a way to put a timeout on a query so that if it runs >>> longer than 5 minutes or something it is just automatically >>> terminated? > > > >
I asked the same question some time ago, and IIRC the answer was that the statement timeout only applies to interactive sessions. So autovacuum would not be affected, but a vacuum run through psql yes. You can also set it for a user (see "alter user ... set ..."), and use separate users for application access and maintenance work. Cheers, Csaba. On Fri, 2006-03-03 at 11:03, Rick Gigger wrote: > Oh that will abort vacuum after that time as well? Can anyone > confirm that this is the case? There shouldn't be ANY queries that > take that long and if there are then can manually set the parameter > when those requests happen. I would prefer to limit by default and > allow longer queries only when I specify. But if it kills vacuum I > will have to take a different approach. > > On Mar 3, 2006, at 2:59 AM, Ragnar wrote: > > > On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: > >> Never-mind that. I'm assuming statement_timeout is what I need? > > > > Yes, but take care if you change this in postgresql.conf: > > some queries might reasonaby be expected to take longer > > than 5 minutes, such as VACUUM. > > > > gnari > > > >> On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: > >> > >>> Is there a way to put a timeout on a query so that if it runs > >>> longer than 5 minutes or something it is just automatically > >>> terminated? > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
I assume that running the vacuumdb command is the same as running it through psql? On Mar 3, 2006, at 3:14 AM, Csaba Nagy wrote: > I asked the same question some time ago, and IIRC the answer was that > the statement timeout only applies to interactive sessions. So > autovacuum would not be affected, but a vacuum run through psql yes. > You can also set it for a user (see "alter user ... set ..."), and use > separate users for application access and maintenance work. > > Cheers, > Csaba. > > > On Fri, 2006-03-03 at 11:03, Rick Gigger wrote: >> Oh that will abort vacuum after that time as well? Can anyone >> confirm that this is the case? There shouldn't be ANY queries that >> take that long and if there are then can manually set the parameter >> when those requests happen. I would prefer to limit by default and >> allow longer queries only when I specify. But if it kills vacuum I >> will have to take a different approach. >> >> On Mar 3, 2006, at 2:59 AM, Ragnar wrote: >> >>> On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: >>>> Never-mind that. I'm assuming statement_timeout is what I need? >>> >>> Yes, but take care if you change this in postgresql.conf: >>> some queries might reasonaby be expected to take longer >>> than 5 minutes, such as VACUUM. >>> >>> gnari >>> >>>> On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: >>>> >>>>> Is there a way to put a timeout on a query so that if it runs >>>>> longer than 5 minutes or something it is just automatically >>>>> terminated? >>> >>> >>> >>> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings > >
On Fri, 2006-03-03 at 21:14, Rick Gigger wrote: > I assume that running the vacuumdb command is the same as running it > through psql? Well, you either run it through psql, or not :-) Seriously, I understand that any client session is affected by the statement timeout settings, doesn't matter what the statement is. But autovacuum is built in starting with 8.1, so it is not affected by statement-timeout. The contrib autovacuum from versions < 8.1 is in turn a regular client, so it is affected. Cheers, Csaba.