Home • Explore • Blog • CI
☰



postgresql
32th chunk of `doc/src/sgml/fdwhandler.sgml`
1c9449bc5e41312c69056831f8775d4e4c9c1b65796660b70000000100000c2a

     usually worthwhile for the FDW to perform row-level locking with as
     close an approximation as practical to the semantics used in
     ordinary <productname>PostgreSQL</productname> tables.  There are multiple
     considerations involved in this.
    </para>

    <para>
     One key decision to be made is whether to perform <firstterm>early
     locking</firstterm> or <firstterm>late locking</firstterm>.  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 <productname>PostgreSQL</productname> TIDs do.
    </para>

    <para>
     By default, <productname>PostgreSQL</productname> 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 <xref linkend="fdw-callbacks-row-locking"/>, which were added
     in <productname>PostgreSQL</productname> 9.5, allow an FDW to use late locking if
     it wishes.
    </para>

    <para>
     An additional consideration is that in <literal>READ COMMITTED</literal>
     isolation mode, <productname>PostgreSQL</productname> 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 <productname>PostgreSQL</productname> 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.
    </para>

    <para>
     For an <command>UPDATE</command> or <command>DELETE</command> on a foreign table, it
     is recommended that the <literal>ForeignScan</literal> operation on the target
     table perform early locking on the
Title: Row Locking: Early vs. Late Locking and Rechecking Conditions
Summary
This section delves into the intricacies of row locking in Foreign Data Wrappers (FDWs). It highlights the decision between early and late locking, where early locking is simpler but potentially leads to reduced concurrency, while late locking requires the ability to uniquely re-identify rows. Furthermore, it discusses how PostgreSQL handles re-checking restriction and join conditions in READ COMMITTED isolation mode, noting the default approach of including the entire row fetched from a foreign table in the column list projected through the join to avoid special demands on the FDW, and it recommends early locking for UPDATE or DELETE operations on the target table.