Docs: improve warnings about nextval() not producing gapless sequences.

tglsfdc · tglsfdc · commit 691d99de386a · 2016-05-07T13:16:50.000-04:00
In the documentation for nextval(), point out explicitly that INSERT ...
ON CONFLICT will call nextval() if needed for the insertion case, whether
or not it ends up following the ON CONFLICT path.  This seems to be a
matter of some confusion, cf bug #14126, so let's be clear about it.

Also mention the issue in the CREATE SEQUENCE reference page, since that
is another place where people might expect such things to be covered.

Minor wording improvements nearby, as well.

Back-patch to 9.5 where ON CONFLICT was introduced.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
@@ -11512,13 +11512,19 @@ nextval('foo'::text)      <lineannotation><literal>foo</literal> is looked up at
 
        <important>
         <para>
-         To avoid blocking concurrent transactions that obtain numbers from the
-         same sequence, a <function>nextval</function> operation is never
+         To avoid blocking concurrent transactions that obtain numbers from
+         the same sequence, a <function>nextval</function> operation is never
          rolled back; that is, once a value has been fetched it is considered
-         used, even if the transaction that did the
-         <function>nextval</function> later aborts.  This means that aborted
-         transactions might leave unused <quote>holes</quote> in the sequence
-         of assigned values.
+         used and will not be returned again.  This is true even if the
+         surrounding transaction later aborts, or if the calling query ends
+         up not using the value.  For example an <command>INSERT</> with
+         an <literal>ON CONFLICT</> clause will compute the to-be-inserted
+         tuple, including doing any required <function>nextval</function>
+         calls, before detecting any conflict that would cause it to follow
+         the <literal>ON CONFLICT</> rule instead.  Such cases will leave
+         unused <quote>holes</quote> in the sequence of assigned values.
+         Thus, <productname>PostgreSQL</> sequence objects <emphasis>cannot
+         be used to obtain <quote>gapless</> sequences</emphasis>.
         </para>
        </important>
 
@@ -11547,8 +11553,8 @@ nextval('foo'::text)      <lineannotation><literal>foo</literal> is looked up at
         Return the value most recently returned by
         <function>nextval</> in the current session. This function is
         identical to <function>currval</function>, except that instead
-        of taking the sequence name as an argument it fetches the
-        value of the last sequence used by <function>nextval</function>
+        of taking the sequence name as an argument it refers to whichever
+        sequence <function>nextval</function> was most recently applied to
         in the current session. It is an error to call
         <function>lastval</function> if <function>nextval</function>
         has not yet been called in the current session.
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
@@ -239,6 +239,16 @@ SELECT * FROM <replaceable>name</replaceable>;
    (-9223372036854775808 to 9223372036854775807).
   </para>
 
+  <para>
+   Because <function>nextval</> and <function>setval</> calls are never
+   rolled back, sequence objects cannot be used if <quote>gapless</>
+   assignment of sequence numbers is needed.  It is possible to build
+   gapless assignment by using exclusive locking of a table containing a
+   counter; but this solution is much more expensive than sequence
+   objects, especially if many transactions need sequence numbers
+   concurrently.
+  </para>
+
   <para>
    Unexpected results might be obtained if a <replaceable
    class="parameter">cache</replaceable> setting greater than one is
