Re: idle_in_transaction_timeout - Mailing list pgsql-hackers
| From | David G Johnston |
|---|---|
| Subject | Re: idle_in_transaction_timeout |
| Date | |
| Msg-id | CAKFQuwYFRSqymSDgcq0BtpJprrJxcjQxyncsOEsaKW-wG9Ni4g@mail.gmail.com Whole thread Raw |
| In response to | Re: idle_in_transaction_timeout (Robert Haas <robertmhaas@gmail.com>) |
| List | pgsql-hackers |
On Tue, Jun 24, 2014 at 10:05 AM, Robert Haas [via PostgreSQL] <[hidden email]> wrote:
On Tue, Jun 24, 2014 at 9:18 AM, Vik Fearing <[hidden email]> wrote:I think we are. First, the correct timeout is a matter of
> 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.
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: Re: idle_in_transaction_timeout
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
pgsql-hackers by date: