Thanks for the responses. > > If this is just going to apply to postgres_fdw why not just have that module > provide a function "disconnect_open_sessions()" or the like that does this > upon user command? I suppose there would be some potential value to having > this be set per-user but that wouldn't preclude the usefulness of a function. > And by having a function the usefulness of the GUC seems reduced. >
The idea of having module-specific functions to remove cached entries seems like a good idea. Users have to frequently call these functions to clean up the cached entries in a long lasting single session. This may not be always possible if these sessions are from an application not from a psql-like client which is a more frequent scenario in the customer use cases. In this case users might have to change their application code that is issuing queries to postgres server to include these functions. Assuming the fact that the server/session configuration happens much before the user application starts to submit actual database queries, having a GUC just helps to avoid making such function calls in between the session, by having to set the GUC either to true if required to cache connections or to off if not to cache connections. > > On a related note is there any entanglement here with the supplied dblink > and/or dblink_fdw [1] modules as they do provide connect and disconnect > functions and also leverages postgres_fdw (or dblink_fdw if specified, which > brings us back to the question of whether this option should be respected by > that FDW). > I found that dblink also has the connection caching concept and it does provide a user a function to disconnect/remove cached connections using a function, dblink_disconnect() using connection name as it's input. IMO, this solution seems a bit problematic as explained in my first response in this mail. The postgres_fdw connection caching and dblink connection caching has no link at all. Please point me if I'm missing anything here. But probably, this GUC can be extended from a bool to an enum of type config_enum_entry and use it for dblink as well. This is extensible as well. Please let me know if this is okay, so that I can code for it. > > Otherwise, I would imagine that having multiple queries execute before > wanting to drop the connection would be desirable so at minimum a test case > that does something like: > > SELECT count(*) FROM remote.tbl1; > -- connection still open > SET enable_connectioncache TO false; > SELECT count(*) FROM remote.tbl2; > -- now it was closed > > Or maybe even better, have the close action happen on a transaction boundary. > This is a valid scenario, as the same connection can be used in the same transaction multiple times. With my attached initial patch above the point is already covered. The decision to cache or not cache the connection happens at the main transaction end i.e. in pgfdw_xact_callback(). > > And if it doesn't just apply to postgres_fdw (or at least doesn't have to) > then the description text should be less specific. > If we are agreed on a generic GUC for postgres_fdw, dblink and so on. I will change the description and documentation accordingly. Thoughts? With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
