Re: idle_in_transaction_timeout

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

> On Tue, Jun 24, 2014 at 10:50 AM, Vik Fearing <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5808915&i=0>> wrote:
>
> > On 06/24/2014 04:04 PM, Robert Haas wrote:
> >>> 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".
> >
> > So how would the local transaction ever get its work done? What option
> > does it have to tell the remote server that it isn't actually idling, it
> > just doesn't need to use the remote connection for a while?
>
> It *is* idling. You're going to get bloat, and lock contention, and
> so on, just as you would for any other idle session.
>
>
If an application is making use of the foreign server directly then there
is the option to commit after using the foreign server, while saving the
relevant data for the main transaction. But if you make use of API
functions there can only be a single transaction encompassing both the
local and foreign servers. But even then, if the user needs a logical
super-transaction across both servers - even though the bulk of the work
occurs locally - that option to commit is then removed regardless of client
usage.

IMO this tool is too blunt to properly allow servers to self-manage
fdw-initiated transactions/sessions; and allowing it to be used is asking
for end-user confusion and frustration.

OTOH, requiring the administrator of the foreign server to issue an ALTER
ROLE fdw_user SET idle_in_transaction_session_timeout = 0; would be fairly
easy to justify. Allowing them to distinguish between known long-running
and problematic transactions and those that are expected to execute quickly
has value as well.

Ultimately you give the users power and then just need to make sure we
provide sufficient documentation suggestions on how best to configure the
two servers in various typical usage scenarios.

David J.

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