Обсуждение: timeouts on transactions etc?
Does Postgres have any mechanisms where one can set an activity timeout, say either dynamically thru SQL to affect a current session, or alternately in a configuration file so to take effect globally? I mean for example so we can tell Postgres to automatically abort/rollback a current statement or transaction if it is still running after 5 seconds? It would return an error / throw an exception at the same time, as if there was a failure or constraint violation for some other reason, so the user would know. Or a generalization of this would be the DBMS enforcing particular resource limits, but I suspect that just clock time is a relatively easy one to do, as it could be implemented with ordinary timers and signals/interrupts. Purposes of this feature include coping with applications that are not well-behaved such as by failing to explicitly end transactions or by asking the DBMS to do too much at once. If so, where is this documented? If not, how much work might it be to add this? I'm looking for something enforced by the DBMS itself, not that an application or bridge layer should do. -- Darren Duncan
On May 28, 2011, at 7:55 PM, Darren Duncan wrote: > Does Postgres have any mechanisms where one can set an activity timeout, say either dynamically thru SQL to affect a currentsession, or alternately in a configuration file so to take effect globally? > > I mean for example so we can tell Postgres to automatically abort/rollback a current statement or transaction if it isstill running after 5 seconds? It would return an error / throw an exception at the same time, as if there was a failureor constraint violation for some other reason, so the user would know. > > Or a generalization of this would be the DBMS enforcing particular resource limits, but I suspect that just clock timeis a relatively easy one to do, as it could be implemented with ordinary timers and signals/interrupts. > > Purposes of this feature include coping with applications that are not well-behaved such as by failing to explicitly endtransactions or by asking the DBMS to do too much at once. > > If so, where is this documented? If not, how much work might it be to add this? > > I'm looking for something enforced by the DBMS itself, not that an application or bridge layer should do. You're looking for "statement_timeout", I think. You can set that globally, but it's better to set it just in the sessionswhere you want it. http://www.postgresql.org/docs/9.0/static/runtime-config-client.html There's also the ability to log long statements, so you can identify and fix bad queries without breaking functionality -log_min_duration_statement and friends. Cheers, Steve
Thank you, Steve, That answers the part about statement times. But what about timeouts for transactions as a whole, ensuring that any transaction, once started, is ended one way or another within X time? -- Darren Duncan Steve Atkins wrote: > On May 28, 2011, at 7:55 PM, Darren Duncan wrote: > >> Does Postgres have any mechanisms where one can set an activity timeout, say either dynamically thru SQL to affect a currentsession, or alternately in a configuration file so to take effect globally? >> >> I mean for example so we can tell Postgres to automatically abort/rollback a current statement or transaction if it isstill running after 5 seconds? It would return an error / throw an exception at the same time, as if there was a failureor constraint violation for some other reason, so the user would know. >> >> Or a generalization of this would be the DBMS enforcing particular resource limits, but I suspect that just clock timeis a relatively easy one to do, as it could be implemented with ordinary timers and signals/interrupts. >> >> Purposes of this feature include coping with applications that are not well-behaved such as by failing to explicitly endtransactions or by asking the DBMS to do too much at once. >> >> If so, where is this documented? If not, how much work might it be to add this? >> >> I'm looking for something enforced by the DBMS itself, not that an application or bridge layer should do. > > You're looking for "statement_timeout", I think. You can set that globally, but it's better to set it just in the sessionswhere you want it. > > http://www.postgresql.org/docs/9.0/static/runtime-config-client.html > > There's also the ability to log long statements, so you can identify and fix bad queries without breaking functionality- log_min_duration_statement and friends. > > Cheers, > Steve