59.5. Row Locking in Foreign Data Wrappers

59.5. Row Locking in Foreign Data Wrappers
Prev	Up	Chapter 59. Writing a Foreign Data Wrapper	Home	Next

+ If an FDW's underlying storage mechanism has a concept of locking + individual rows to prevent concurrent updates of those rows, it is + usually worthwhile for the FDW to perform row-level locking with as + close an approximation as practical to the semantics used in + ordinary PostgreSQL tables. There are multiple + considerations involved in this. +

+ One key decision to be made is whether to perform early + locking or late locking. In early locking, a row is + locked when it is first retrieved from the underlying store, while in + late locking, the row is locked only when it is known that it needs to + be locked. (The difference arises because some rows may be discarded by + locally-checked restriction or join conditions.) Early locking is much + simpler and avoids extra round trips to a remote store, but it can cause + locking of rows that need not have been locked, resulting in reduced + concurrency or even unexpected deadlocks. Also, late locking is only + possible if the row to be locked can be uniquely re-identified later. + Preferably the row identifier should identify a specific version of the + row, as PostgreSQL TIDs do. +

+ By default, PostgreSQL ignores locking considerations + when interfacing to FDWs, but an FDW can perform early locking without + any explicit support from the core code. The API functions described + in Section 59.2.6, which were added + in PostgreSQL 9.5, allow an FDW to use late locking if + it wishes. +

+ An additional consideration is that in READ COMMITTED + isolation mode, PostgreSQL may need to re-check + restriction and join conditions against an updated version of some + target tuple. Rechecking join conditions requires re-obtaining copies + of the non-target rows that were previously joined to the target tuple. + When working with standard PostgreSQL tables, this is + done by including the TIDs of the non-target tables in the column list + projected through the join, and then re-fetching non-target rows when + required. This approach keeps the join data set compact, but it + requires inexpensive re-fetch capability, as well as a TID that can + uniquely identify the row version to be re-fetched. By default, + therefore, the approach used with foreign tables is to include a copy of + the entire row fetched from a foreign table in the column list projected + through the join. This puts no special demands on the FDW but can + result in reduced performance of merge and hash joins. An FDW that is + capable of meeting the re-fetch requirements can choose to do it the + first way. +

+ For an UPDATE or DELETE on a foreign table, it + is recommended that the ForeignScan operation on the target + table perform early locking on the rows that it fetches, perhaps via the + equivalent of SELECT FOR UPDATE. An FDW can detect whether + a table is an UPDATE/DELETE target at plan time + by comparing its relid to root->parse->resultRelation, + or at execution time by using ExecRelationIsTargetRelation(). + An alternative possibility is to perform late locking within the + ExecForeignUpdate or ExecForeignDelete + callback, but no special support is provided for this. +

+ For foreign tables that are specified to be locked by a SELECT + FOR UPDATE/SHARE command, the ForeignScan operation can + again perform early locking by fetching tuples with the equivalent + of SELECT FOR UPDATE/SHARE. To perform late locking + instead, provide the callback functions defined + in Section 59.2.6. + In GetForeignRowMarkType, select rowmark option + ROW_MARK_EXCLUSIVE, ROW_MARK_NOKEYEXCLUSIVE, + ROW_MARK_SHARE, or ROW_MARK_KEYSHARE depending + on the requested lock strength. (The core code will act the same + regardless of which of these four options you choose.) + Elsewhere, you can detect whether a foreign table was specified to be + locked by this type of command by using get_plan_rowmark at + plan time, or ExecFindRowMark at execution time; you must + check not only whether a non-null rowmark struct is returned, but that + its strength field is not LCS_NONE. +

+ Lastly, for foreign tables that are used in an UPDATE, + DELETE or SELECT FOR UPDATE/SHARE command but + are not specified to be row-locked, you can override the default choice + to copy entire rows by having GetForeignRowMarkType select + option ROW_MARK_REFERENCE when it sees lock strength + LCS_NONE. This will cause RefetchForeignRow to + be called with that value for markType; it should then + re-fetch the row without acquiring any new lock. (If you have + a GetForeignRowMarkType function but don't wish to re-fetch + unlocked rows, select option ROW_MARK_COPY + for LCS_NONE.) +

+ See src/include/nodes/lockoptions.h, the comments + for RowMarkType and PlanRowMark + in src/include/nodes/plannodes.h, and the comments for + ExecRowMark in src/include/nodes/execnodes.h for + additional information. +

Prev	Up	Next
59.4. Foreign Data Wrapper Query Planning	Home	Chapter 60. Writing a Table Sampling Method