Menu
▾
▴
#1 Terminate transactions after scan
Hello,
I've noticed the pgsql_fdw opens a transaction as soon as it connects but it never closes it intentionally. This can be problematic as the remote session would keep locks to the queried tables. The attached patch only keep the transaction open for the duration of the cursor scan.
I've changed the patch as I've seen that the session can open many cursors concurrently: closing the transaction at each cursor means that when the first is closed, all the other get killed.
The attached patch keeps a reference count: the transaction is open as soon as a cursor is required, and is terminated when the last cursor is closed.
Uhm... the patch is still broken as the refcount should be not global but attached to the connection. Let me see if I can fix it....
attempt 3
attempt 3
New implementation of the patch: use the connections refcount to monitor the connection usage. The transaction is started by the first cursor using it (either on a new connection or a recycled one) and closed when the last cursor is closed.
The patch consists of the 2 commits marked as "attempt 3": the first commit implements the transaction handling, the second is an optimization: no connection is started at planning time as no cursor is needed.
Thanks for your proposal.
As you say, pgsql_fdw used one transaction until the backend dies, or local transaction aborts.
This issue had been discussed in pgsql-hackers list during developing pgsql_fdw for 9.2, and we reached the conclusion that pgsql_fdw should use individual REPEATABLE READ or SERIALIZABLE transaction for each local query.
We've merged our works which are done for pgsql_fdw in 9.2 development cycle, including finer-grained remote transaction, on May 9. Sorry for missing your proposal. Please try latest version of pgsql_fdw.