Postgresql 中文操作指南

59.5. Row Locking in Foreign Data Wrappers #

如果 FDW 的底层存储机制具有锁定个别行以防止对这些行进行并发更新的概念，那么通常值得 FDW 对行级锁定进行尽可能接近普通 PostgreSQL 表中使用的语义的近似处理。其中涉及多个考虑因素。

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.

需要做出的一个关键决策是执行 early locking 还是 late locking。在早期锁定中，当行首次从底层存储中检索到时，该行将被锁定，而在后期锁定中，仅当已知需要锁定该行时才锁定该行。（出现差异的原因是某些行可能会被本地检查的限制或联接条件丢弃。）早期锁定要简单得多，并且避免了对远程存储的额外往返，但它可能会导致锁定不必锁定的行，从而导致并发性降低，甚至出现意外的死锁。此外，只有当待锁定的行以后可以被唯一重新识别时，才可能进行后期锁定。最好是行标识符应该标识行的特定版本，就像 PostgreSQL TID 所做的那样。

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.

默认情况下，PostgreSQL在与FDW交互时忽略锁定注意事项，但FDW可以在没有任何来自核心代码的显式支持的情况下执行早期锁定。 Section 59.2.6中描述的API函数（在PostgreSQL 9.5中添加）允许FDW在希望时使用延迟锁定。

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.

另一个考虑因素是，在 READ COMMITTED 隔离模式中，PostgreSQL 可能需要针对某些目标元组的更新版本重新检查限制和连接条件。重新检查联接条件需要重新获取以前与目标元组联接的非目标行的副本。在使用标准 PostgreSQL 表时，这是通过将非目标表的 TID 包含在通过联接投影的列列表中来完成的，然后在需要时重新获取非目标行。此方法使联接数据集保持紧凑，但它需要廉价的重新获取功能以及能够唯一标识要重新获取的行版本的 TID。因此，默认情况下，与外部表配合使用的方法是将从外部表获取的整行副本包含在通过联接投影的列列表中。这不会对 FDW 提出任何特殊要求，但可能会导致合并和哈希联接的性能降低。能够满足重新获取要求的 FDW 可以选择以第一种方式进行。

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.

对于外部表上的 UPDATE 或 DELETE，建议目标表上的 ForeignScan 操作对其获取的行执行早期锁定，可能通过相当于 SELECT FOR UPDATE 的方式。FDW 可以通过将表 relid 与 root→parse→resultRelation_进行比较来在计划时检测表是否是 _UPDATE/DELETE 目标，或在执行时使用 ExecRelationIsTargetRelation() 进行检测。另一种可能性是在 ExecForeignUpdate 或 ExecForeignDelete 回调中执行后期锁定，但没有为此提供特殊支持。

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.

对于指定由_SELECT FOR UPDATE/SHARE_命令锁定的外部表，ForeignScan_操作可以通过使用等效于_SELECT FOR UPDATE/SHARE_的元组再次执行早期锁定。要改为执行延迟锁定，请提供 Section 59.2.6中定义的回调函数。在_GetForeignRowMarkType_中，根据请求的锁定强度，选择行标记选项_ROW_MARK_EXCLUSIVE，ROW_MARK_NOKEYEXCLUSIVE，ROW_MARK_SHARE_或_ROW_MARK_KEYSHARE。(无论选择哪四个选项之一，核心代码的行为都相同。)在其他地方，你可以使用_get_plan_rowmark_在规划时或_ExecFindRowMark_在执行时检测外部表是否已指定由这种类型的命令锁定；必须检查不只是返回非空行标记结构，并且其_strength_字段不是_LCS_NONE_。

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.

最后，对于用于 UPDATE、DELETE 或 SELECT FOR UPDATE/SHARE 命令但未指定为行锁定的外部表，您可以通过让 GetForeignRowMarkType 在看到锁定强度为 LCS_NONE 时选择选项 ROW_MARK_REFERENCE 来覆盖复制整行的默认选择。这将导致针对 markType 调用 RefetchForeignRow 时具有该值；它应该在不获取任何新锁的情况下重新获取行。（如果您有 GetForeignRowMarkType 函数但不希望重新获取解锁的行，则为 LCS_NONE 选择选项 ROW_MARK_COPY。）

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.)

有关更多信息，请参见 src/include/nodes/lockoptions.h、src/include/nodes/plannodes.h 中 RowMarkType 和 PlanRowMark 的注释以及 src/include/nodes/execnodes.h 中 ExecRowMark 的注释。

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.