diff options
Diffstat (limited to 'doc/src/sgml/html/fdw-row-locking.html')
| -rw-r--r-- | doc/src/sgml/html/fdw-row-locking.html | 93 |
1 files changed, 93 insertions, 0 deletions
diff --git a/doc/src/sgml/html/fdw-row-locking.html b/doc/src/sgml/html/fdw-row-locking.html new file mode 100644 index 0000000..ad43691 --- /dev/null +++ b/doc/src/sgml/html/fdw-row-locking.html @@ -0,0 +1,93 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>59.5. Row Locking in Foreign Data Wrappers</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="fdw-planning.html" title="59.4. Foreign Data Wrapper Query Planning" /><link rel="next" href="tablesample-method.html" title="Chapter 60. Writing a Table Sampling Method" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">59.5. Row Locking in Foreign Data Wrappers</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="fdw-planning.html" title="59.4. Foreign Data Wrapper Query Planning">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="fdwhandler.html" title="Chapter 59. Writing a Foreign Data Wrapper">Up</a></td><th width="60%" align="center">Chapter 59. Writing a Foreign Data Wrapper</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="tablesample-method.html" title="Chapter 60. Writing a Table Sampling Method">Next</a></td></tr></table><hr /></div><div class="sect1" id="FDW-ROW-LOCKING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">59.5. Row Locking in Foreign Data Wrappers <a href="#FDW-ROW-LOCKING" class="id_link">#</a></h2></div></div></div><p> + 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 <span class="productname">PostgreSQL</span> tables. There are multiple + considerations involved in this. + </p><p> + One key decision to be made is whether to perform <em class="firstterm">early + locking</em> or <em class="firstterm">late locking</em>. 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 <span class="productname">PostgreSQL</span> TIDs do. + </p><p> + By default, <span class="productname">PostgreSQL</span> 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 <a class="xref" href="fdw-callbacks.html#FDW-CALLBACKS-ROW-LOCKING" title="59.2.6. FDW Routines for Row Locking">Section 59.2.6</a>, which were added + in <span class="productname">PostgreSQL</span> 9.5, allow an FDW to use late locking if + it wishes. + </p><p> + An additional consideration is that in <code class="literal">READ COMMITTED</code> + isolation mode, <span class="productname">PostgreSQL</span> 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 <span class="productname">PostgreSQL</span> 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. + </p><p> + For an <code class="command">UPDATE</code> or <code class="command">DELETE</code> on a foreign table, it + is recommended that the <code class="literal">ForeignScan</code> operation on the target + table perform early locking on the rows that it fetches, perhaps via the + equivalent of <code class="command">SELECT FOR UPDATE</code>. An FDW can detect whether + a table is an <code class="command">UPDATE</code>/<code class="command">DELETE</code> target at plan time + by comparing its relid to <code class="literal">root->parse->resultRelation</code>, + or at execution time by using <code class="function">ExecRelationIsTargetRelation()</code>. + An alternative possibility is to perform late locking within the + <code class="function">ExecForeignUpdate</code> or <code class="function">ExecForeignDelete</code> + callback, but no special support is provided for this. + </p><p> + For foreign tables that are specified to be locked by a <code class="command">SELECT + FOR UPDATE/SHARE</code> command, the <code class="literal">ForeignScan</code> operation can + again perform early locking by fetching tuples with the equivalent + of <code class="command">SELECT FOR UPDATE/SHARE</code>. To perform late locking + instead, provide the callback functions defined + in <a class="xref" href="fdw-callbacks.html#FDW-CALLBACKS-ROW-LOCKING" title="59.2.6. FDW Routines for Row Locking">Section 59.2.6</a>. + In <code class="function">GetForeignRowMarkType</code>, select rowmark option + <code class="literal">ROW_MARK_EXCLUSIVE</code>, <code class="literal">ROW_MARK_NOKEYEXCLUSIVE</code>, + <code class="literal">ROW_MARK_SHARE</code>, or <code class="literal">ROW_MARK_KEYSHARE</code> 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 <code class="function">get_plan_rowmark</code> at + plan time, or <code class="function">ExecFindRowMark</code> at execution time; you must + check not only whether a non-null rowmark struct is returned, but that + its <code class="structfield">strength</code> field is not <code class="literal">LCS_NONE</code>. + </p><p> + Lastly, for foreign tables that are used in an <code class="command">UPDATE</code>, + <code class="command">DELETE</code> or <code class="command">SELECT FOR UPDATE/SHARE</code> command but + are not specified to be row-locked, you can override the default choice + to copy entire rows by having <code class="function">GetForeignRowMarkType</code> select + option <code class="literal">ROW_MARK_REFERENCE</code> when it sees lock strength + <code class="literal">LCS_NONE</code>. This will cause <code class="function">RefetchForeignRow</code> to + be called with that value for <code class="structfield">markType</code>; it should then + re-fetch the row without acquiring any new lock. (If you have + a <code class="function">GetForeignRowMarkType</code> function but don't wish to re-fetch + unlocked rows, select option <code class="literal">ROW_MARK_COPY</code> + for <code class="literal">LCS_NONE</code>.) + </p><p> + See <code class="filename">src/include/nodes/lockoptions.h</code>, the comments + for <code class="type">RowMarkType</code> and <code class="type">PlanRowMark</code> + in <code class="filename">src/include/nodes/plannodes.h</code>, and the comments for + <code class="type">ExecRowMark</code> in <code class="filename">src/include/nodes/execnodes.h</code> for + additional information. + </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="fdw-planning.html" title="59.4. Foreign Data Wrapper Query Planning">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="fdwhandler.html" title="Chapter 59. Writing a Foreign Data Wrapper">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="tablesample-method.html" title="Chapter 60. Writing a Table Sampling Method">Next</a></td></tr><tr><td width="40%" align="left" valign="top">59.4. Foreign Data Wrapper Query Planning </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 60. Writing a Table Sampling Method</td></tr></table></div></body></html>
\ No newline at end of file |