codekingpro/portable-devtools

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>64.5. Index Uniqueness Checks</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="index-locking.html" title="64.4. Index Locking Considerations" /><link rel="next" href="index-cost-estimation.html" title="64.6. Index Cost Estimation Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">64.5. Index Uniqueness Checks</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="index-locking.html" title="64.4. Index Locking Considerations">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">Up</a></td><th width="60%" align="center">Chapter 64. Index Access Method Interface Definition</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="index-cost-estimation.html" title="64.6. Index Cost Estimation Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="INDEX-UNIQUE-CHECKS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">64.5. Index Uniqueness Checks <a href="#INDEX-UNIQUE-CHECKS" class="id_link">#</a></h2></div></div></div><p>3   <span class="productname">PostgreSQL</span> enforces SQL uniqueness constraints4   using <em class="firstterm">unique indexes</em>, which are indexes that disallow5   multiple entries with identical keys.  An access method that supports this6   feature sets <code class="structfield">amcanunique</code> true.7   (At present, only b-tree supports it.)  Columns listed in the8   <code class="literal">INCLUDE</code> clause are not considered when enforcing9   uniqueness.10  </p><p>11   Because of MVCC, it is always necessary to allow duplicate entries to12   exist physically in an index: the entries might refer to successive13   versions of a single logical row.  The behavior we actually want to14   enforce is that no MVCC snapshot could include two rows with equal15   index keys.  This breaks down into the following cases that must be16   checked when inserting a new row into a unique index:17 18    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>19       If a conflicting valid row has been deleted by the current transaction,20       it's okay.  (In particular, since an UPDATE always deletes the old row21       version before inserting the new version, this will allow an UPDATE on22       a row without changing the key.)23      </p></li><li class="listitem"><p>24       If a conflicting row has been inserted by an as-yet-uncommitted25       transaction, the would-be inserter must wait to see if that transaction26       commits.  If it rolls back then there is no conflict.  If it commits27       without deleting the conflicting row again, there is a uniqueness28       violation.  (In practice we just wait for the other transaction to29       end and then redo the visibility check in toto.)30      </p></li><li class="listitem"><p>31       Similarly, if a conflicting valid row has been deleted by an32       as-yet-uncommitted transaction, the would-be inserter must wait33       for that transaction to commit or abort, and then repeat the test.34      </p></li></ul></div><p>35  </p><p>36   Furthermore, immediately before reporting a uniqueness violation37   according to the above rules, the access method must recheck the38   liveness of the row being inserted.  If it is committed dead then39   no violation should be reported.  (This case cannot occur during the40   ordinary scenario of inserting a row that's just been created by41   the current transaction.  It can happen during42   <code class="command">CREATE UNIQUE INDEX CONCURRENTLY</code>, however.)43  </p><p>44   We require the index access method to apply these tests itself, which45   means that it must reach into the heap to check the commit status of46   any row that is shown to have a duplicate key according to the index47   contents.  This is without a doubt ugly and non-modular, but it saves48   redundant work: if we did a separate probe then the index lookup for49   a conflicting row would be essentially repeated while finding the place to50   insert the new row's index entry.  What's more, there is no obvious way51   to avoid race conditions unless the conflict check is an integral part52   of insertion of the new index entry.53  </p><p>54   If the unique constraint is deferrable, there is additional complexity:55   we need to be able to insert an index entry for a new row, but defer any56   uniqueness-violation error until end of statement or even later.  To57   avoid unnecessary repeat searches of the index, the index access method58   should do a preliminary uniqueness check during the initial insertion.59   If this shows that there is definitely no conflicting live tuple, we60   are done.  Otherwise, we schedule a recheck to occur when it is time to61   enforce the constraint.  If, at the time of the recheck, both the inserted62   tuple and some other tuple with the same key are live, then the error63   must be reported.  (Note that for this purpose, <span class="quote">“<span class="quote">live</span>”</span> actually64   means <span class="quote">“<span class="quote">any tuple in the index entry's HOT chain is live</span>”</span>.)65   To implement this, the <code class="function">aminsert</code> function is passed a66   <code class="literal">checkUnique</code> parameter having one of the following values:67 68    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>69       <code class="literal">UNIQUE_CHECK_NO</code> indicates that no uniqueness checking70       should be done (this is not a unique index).71      </p></li><li class="listitem"><p>72       <code class="literal">UNIQUE_CHECK_YES</code> indicates that this is a non-deferrable73       unique index, and the uniqueness check must be done immediately, as74       described above.75      </p></li><li class="listitem"><p>76       <code class="literal">UNIQUE_CHECK_PARTIAL</code> indicates that the unique77       constraint is deferrable. <span class="productname">PostgreSQL</span>78       will use this mode to insert each row's index entry.  The access79       method must allow duplicate entries into the index, and report any80       potential duplicates by returning false from <code class="function">aminsert</code>.81       For each row for which false is returned, a deferred recheck will82       be scheduled.83      </p><p>84       The access method must identify any rows which might violate the85       unique constraint, but it is not an error for it to report false86       positives. This allows the check to be done without waiting for other87       transactions to finish; conflicts reported here are not treated as88       errors and will be rechecked later, by which time they may no longer89       be conflicts.90      </p></li><li class="listitem"><p>91       <code class="literal">UNIQUE_CHECK_EXISTING</code> indicates that this is a deferred92       recheck of a row that was reported as a potential uniqueness violation.93       Although this is implemented by calling <code class="function">aminsert</code>, the94       access method must <span class="emphasis"><em>not</em></span> insert a new index entry in this95       case.  The index entry is already present.  Rather, the access method96       must check to see if there is another live index entry.  If so, and97       if the target row is also still live, report error.98      </p><p>99       It is recommended that in a <code class="literal">UNIQUE_CHECK_EXISTING</code> call,100       the access method further verify that the target row actually does101       have an existing entry in the index, and report error if not.  This102       is a good idea because the index tuple values passed to103       <code class="function">aminsert</code> will have been recomputed.  If the index104       definition involves functions that are not really immutable, we105       might be checking the wrong area of the index.  Checking that the106       target row is found in the recheck verifies that we are scanning107       for the same tuple values as were used in the original insertion.108      </p></li></ul></div><p>109  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="index-locking.html" title="64.4. Index Locking Considerations">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexam.html" title="Chapter 64. Index Access Method Interface Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="index-cost-estimation.html" title="64.6. Index Cost Estimation Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">64.4. Index Locking Considerations </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"> 64.6. Index Cost Estimation Functions</td></tr></table></div></body></html>