Re: idle_in_transaction_timeout

On Tue, Jun 24, 2014 at 10:05 AM, Robert Haas [via PostgreSQL] <
ml-node+s1045698n5808893h7(at)n5(dot)nabble(dot)com> wrote:

> On Tue, Jun 24, 2014 at 9:18 AM, Vik Fearing <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5808893&i=0>> wrote:
>
> > On 06/22/2014 05:11 PM, Kevin Grittner wrote:
> >> I found one substantive issue that had been missed in discussion,
> >> though. The patch modifies the postgres_fdw extension to make it
> >> automatically exempt from an attempt to set a limit like this on
> >> the server to which it connects. I'm not sure that's a good idea.
> >> Why should this type of connection be allowed to sit indefinitely
> >> with an idle open transaction? I'm inclined to omit this part of
> >> the patch
> >
> > My reasoning for doing it the way I did is that if a transaction touches
> > a foreign table and then goes bumbling along with other things the
> > transaction is active but the connection to the remote server remains
> > idle in transaction. If it hits the timeout, when the local transaction
> > goes to commit it errors out and you lose all your work.
> >
> > If the local transaction is actually idle in transaction and the local
> > server doesn't have a timeout, we're no worse off than before this
> patch.
>
> I think we are. First, the correct timeout is a matter of
> remote-server-policy, not local-server-policy. If the remote server
> wants to boot people with long-running idle transactions, it's
> entitled to do that, and postgres_fdw shouldn't assume that it's
> "special". The local server policy may be different, and may not even
> have been configured by the same person. Second, setting another GUC
> at every session start adds overhead for all users of postgres_fdw.
>
> Now, it might be that postgres_fdw should have a facility to allow
> arbitrary options to be set on the foreign side at each connection
> startup. Then that could be used here if someone wants this behavior.
> But I don't think we should hard-code it, because it could also be NOT
> what someone wants.
>
>
The missing ability is that while the user only cares about the one logical
session we are dealing with two physical sessions in a parent-child
relationship where the child session state does not match that of its
parent. For me, this whole line of thought is based upon the logical
"idle_in_transaction" - did the application really mean to leave this
hanging?

Say that 90% of the time disabling the timeout will be the correct course
of action; making the user do this explicitly does not seem reasonable.
And if "doesn't matter" is the current state when the foreign server is
configured no setting will be passed. Then if the remote server does
institute a timeout all the relevant configurations will need to be changed.

ISTM that the additional overhead in this case would be very small in
percentage terms; at least enough so that usability would be my default
choice.

I have no problem allowing for user-specified behavior but the default of
disabling the timeout seems reasonable. I am doubting that actually
synchronizing the parent and child sessions, so that the child reports the
same status as the parent, is a valid option - though it would be the
"best" solution since the child would only report IIT if the parent was IIT.

For me, a meaningful default and usability are trumping the unknown
performance degradation. I can go either way on allowing the local
definition to specify its own non-zero timeout but it probably isn't worth
the effort. The foreign server administrator ultimately will have to be
aware of which users are connecting via FDW and address his "long-running
transaction" concerns in a more nuanced way than this parameter allows. In
effect this becomes an 80% solution because it is not (all that) useful on
the remote end of a FDW connection; though at least the local end can make
proper use of it to protect both servers.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/idle-in-transaction-timeout-tp5805859p5808905.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.