2015-11-04 20:35 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: > On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > > 2015-11-04 18:18 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: > >> > >> On Wed, Nov 4, 2015 at 11:15 AM, Pavel Stehule <pavel.steh...@gmail.com > > > >> wrote: > >> > > >> > > >> > 2015-11-04 18:11 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>: > >> >> > >> >> Merlin Moncure <mmonc...@gmail.com> writes: > >> >> >> Yes, and that is what I meant. I have two problems with > >> >> >> transaction_idle_timeout (as opposed to transaction_timeout): > >> >> >> > >> >> >> A) It's more complex. Unsophisticated administrators may not > >> >> >> understand or set it properly > >> >> >> > >> >> >> B) There is no way to enforce an upper bound on transaction time > >> >> >> with > >> >> >> that setting. A pathological application could keep a transaction > >> >> >> open forever without running into any timeouts -- that's a > >> >> >> dealbreaker > >> >> >> for me. > >> >> >> > >> >> >> From my point of view the purpose of the setting should be to > >> >> >> protect > >> >> >> you from any single actor from doing things that damage the > >> >> >> database. > >> >> >> 'idle in transaction' happens to be one obvious way, but upper > bound > >> >> >> on transaction time protects you in general way. > >> >> > >> >> > Note, having both settings would work too. > >> >> > >> >> I'd vote for just transaction_timeout. The way our timeout manager > >> >> logic works, that should be more efficient, as the timeout would only > >> >> have to be established once at transaction start, not every time the > >> >> main command loop iterates. > >> > > >> > > >> > I cannot to say, so transaction_timeout is not useful, but it cannot > be > >> > effective solution for some mentioned issues. With larger data you > >> > cannot to > >> > set transaction_timeout less than few hours. > >> > >> sure. note however any process can manually opt in to a longer timeout. > > > > > > it doesn't help. How I can set transaction_timeout if I have series of > slow > > statements? In this case I cannot to set transaction_timeout before any > > statement or after any success statement. > > Not quite following you. The client has to go: > BEGIN; > SET transaction_timeout = x; > .... >
where is the point when transaction_timeout start? In BEGIN or in SET transaction_timeout ? How I can emulate transaction_idle_timeout? Can I refresh transaction_timeout? My issue isn't long statements, but broken client, that is broken in wrong state - connect is still active, but no any statement will coming. Regards Pavel > or the client can do that on session start up. There are two problem > cases I can think of: > 1) connection pooler (pgbouncer): This can work, but you have to be > very careful. Maybe DISCARD needs to be able to undo adjusted > session settings if it doesn't already. > > 2) procedure emulating functions: It's a major pain that you can't > manage timeout inside a function itself. You also can't manage > transaction state or isolation level. The real solution here is to > implement stored procedures though. > > merlin >
