codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 If an FDW's underlying storage mechanism has a concept of locking4 individual rows to prevent concurrent updates of those rows, it is5 usually worthwhile for the FDW to perform row-level locking with as6 close an approximation as practical to the semantics used in7 ordinary <span class="productname">PostgreSQL</span> tables. There are multiple8 considerations involved in this.9 </p><p>10 One key decision to be made is whether to perform <em class="firstterm">early11 locking</em> or <em class="firstterm">late locking</em>. In early locking, a row is12 locked when it is first retrieved from the underlying store, while in13 late locking, the row is locked only when it is known that it needs to14 be locked. (The difference arises because some rows may be discarded by15 locally-checked restriction or join conditions.) Early locking is much16 simpler and avoids extra round trips to a remote store, but it can cause17 locking of rows that need not have been locked, resulting in reduced18 concurrency or even unexpected deadlocks. Also, late locking is only19 possible if the row to be locked can be uniquely re-identified later.20 Preferably the row identifier should identify a specific version of the21 row, as <span class="productname">PostgreSQL</span> TIDs do.22 </p><p>23 By default, <span class="productname">PostgreSQL</span> ignores locking considerations24 when interfacing to FDWs, but an FDW can perform early locking without25 any explicit support from the core code. The API functions described26 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 added27 in <span class="productname">PostgreSQL</span> 9.5, allow an FDW to use late locking if28 it wishes.29 </p><p>30 An additional consideration is that in <code class="literal">READ COMMITTED</code>31 isolation mode, <span class="productname">PostgreSQL</span> may need to re-check32 restriction and join conditions against an updated version of some33 target tuple. Rechecking join conditions requires re-obtaining copies34 of the non-target rows that were previously joined to the target tuple.35 When working with standard <span class="productname">PostgreSQL</span> tables, this is36 done by including the TIDs of the non-target tables in the column list37 projected through the join, and then re-fetching non-target rows when38 required. This approach keeps the join data set compact, but it39 requires inexpensive re-fetch capability, as well as a TID that can40 uniquely identify the row version to be re-fetched. By default,41 therefore, the approach used with foreign tables is to include a copy of42 the entire row fetched from a foreign table in the column list projected43 through the join. This puts no special demands on the FDW but can44 result in reduced performance of merge and hash joins. An FDW that is45 capable of meeting the re-fetch requirements can choose to do it the46 first way.47 </p><p>48 For an <code class="command">UPDATE</code> or <code class="command">DELETE</code> on a foreign table, it49 is recommended that the <code class="literal">ForeignScan</code> operation on the target50 table perform early locking on the rows that it fetches, perhaps via the51 equivalent of <code class="command">SELECT FOR UPDATE</code>. An FDW can detect whether52 a table is an <code class="command">UPDATE</code>/<code class="command">DELETE</code> target at plan time53 by comparing its relid to <code class="literal">root->parse->resultRelation</code>,54 or at execution time by using <code class="function">ExecRelationIsTargetRelation()</code>.55 An alternative possibility is to perform late locking within the56 <code class="function">ExecForeignUpdate</code> or <code class="function">ExecForeignDelete</code>57 callback, but no special support is provided for this.58 </p><p>59 For foreign tables that are specified to be locked by a <code class="command">SELECT60 FOR UPDATE/SHARE</code> command, the <code class="literal">ForeignScan</code> operation can61 again perform early locking by fetching tuples with the equivalent62 of <code class="command">SELECT FOR UPDATE/SHARE</code>. To perform late locking63 instead, provide the callback functions defined64 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>.65 In <code class="function">GetForeignRowMarkType</code>, select rowmark option66 <code class="literal">ROW_MARK_EXCLUSIVE</code>, <code class="literal">ROW_MARK_NOKEYEXCLUSIVE</code>,67 <code class="literal">ROW_MARK_SHARE</code>, or <code class="literal">ROW_MARK_KEYSHARE</code> depending68 on the requested lock strength. (The core code will act the same69 regardless of which of these four options you choose.)70 Elsewhere, you can detect whether a foreign table was specified to be71 locked by this type of command by using <code class="function">get_plan_rowmark</code> at72 plan time, or <code class="function">ExecFindRowMark</code> at execution time; you must73 check not only whether a non-null rowmark struct is returned, but that74 its <code class="structfield">strength</code> field is not <code class="literal">LCS_NONE</code>.75 </p><p>76 Lastly, for foreign tables that are used in an <code class="command">UPDATE</code>,77 <code class="command">DELETE</code> or <code class="command">SELECT FOR UPDATE/SHARE</code> command but78 are not specified to be row-locked, you can override the default choice79 to copy entire rows by having <code class="function">GetForeignRowMarkType</code> select80 option <code class="literal">ROW_MARK_REFERENCE</code> when it sees lock strength81 <code class="literal">LCS_NONE</code>. This will cause <code class="function">RefetchForeignRow</code> to82 be called with that value for <code class="structfield">markType</code>; it should then83 re-fetch the row without acquiring any new lock. (If you have84 a <code class="function">GetForeignRowMarkType</code> function but don't wish to re-fetch85 unlocked rows, select option <code class="literal">ROW_MARK_COPY</code>86 for <code class="literal">LCS_NONE</code>.)87 </p><p>88 See <code class="filename">src/include/nodes/lockoptions.h</code>, the comments89 for <code class="type">RowMarkType</code> and <code class="type">PlanRowMark</code>90 in <code class="filename">src/include/nodes/plannodes.h</code>, and the comments for91 <code class="type">ExecRowMark</code> in <code class="filename">src/include/nodes/execnodes.h</code> for92 additional information.93 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 60. Writing a Table Sampling Method</td></tr></table></div></body></html>