On Tue, Jun 24, 2014 at 11:11 AM, Robert Haas [via PostgreSQL] <[hidden email]> wrote:
On Tue, Jun 24, 2014 at 10:50 AM, Vik Fearing <[hidden email]> 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.
