Re: idle_in_transaction_timeout - Mailing list pgsql-hackers

On 2014-06-23 13:19:47 -0700, Kevin Grittner wrote:
> >>> which already seems less clear (because the transaction belongs
> >>> to idle)
> 
> I have no idea what that means.

It's "idle_in_transaction"_session_timeout. Not
"idle_in"_transaction_session_timeout.

> >>> and for another that distinction seems to be to subtle for users.
> 
> The difference between an "idle in transaction session" and an
> "idle transaction" is too subtle for someone preparing to terminate
> one of those?

Yes. To me that's an academic distinction. As a nonnative speaker it
looks pretty much random that one has an "in" in it and the other
doesn't. Maybe I'm just having a grammar fail, but there doesn't seem to
be much sense in it.

> >>> The reason I suggested
> >>> idle_in_transaction_termination/cancellation_timeout is that that maps
> >>> nicely to pg_terminate/cancel_backend() and is rather descriptive.
> 
> I've always hated that naming because it is so confusing and
> doesn't describe what is being terminated.

Well, it's what's there. And it doesn't seem to cause much confusion. I
don't see how it get's less confusing by introducing different terminology.

> In my experience, for the same reasons Robert gave much earlier in
> the thread, if both were available the one which would be more
> appropriate for cases I've seen would be the one that closed the
> session with a FATAL message in the log.  If we were only going to
> have one or the other, the one that terminated the session is the
> one that *I* would prefer to see.

I think it's just different usecases. For OLTP clients you probably
primarily want to FATAL the session. But if you interactive sessions
that's much less the case. If some DBA and even more so an analytics guy
has a transaction open he'll have to live with the transaction being
aborted. But you won't be liked for needlessly removing the 10 multi GB
temporary tables with intermediate results. And don't you say protecting
against that isn't necessary - I've seen databases slow to a crawl
because some data analyst  went home over the weekend with a open
transaction after a meeting went on longer than planned.

> > A long idle in transaction state pretty much always indicates a
> > problematic interaction with postgres.
> 
> True.  Which makes me wonder whether we shouldn't default this to
> something non-zero -- even if it is 5 or 10 days.

-1. Can't really say why though. Just seems a bit odd.

> It also gets me
> thinking about whether a good follow-on patch would be a timeout
> for prepared transactions.

I think that might be useful although I think it'd be relatively hard to
implement. I guess that for now monitoring pg_prepared_xacts will have
to suffice.

> BTW, since nobody has commented on the issue of the postgres_fdw
> automatically exempting itself from the timeout, I will plan on
> removing that when the naming argument reaches something resembling
> a conclusion and I go to push this ... unless someone objects
> before that.

Sounds good. I haven't yet seen a justification for it so far...

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services