Re: Sequence's value can be rollback after a crashed recovery. - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Sequence's value can be rollback after a crashed recovery.
Date
Msg-id 61104.1637703666@sss.pgh.pa.us
Whole thread Raw
In response to Re: Sequence's value can be rollback after a crashed recovery.  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Sequence's value can be rollback after a crashed recovery.
Re: Sequence's value can be rollback after a crashed recovery.
List pgsql-hackers
I wrote:
> I wonder though if we shouldn't try to improve the existing text.
> The phrasing "never rolled back" seems like it's too easily
> misinterpreted.  Maybe rewrite the <caution> block like
> ...

A bit of polishing later, maybe like the attached.

            regards, tom lane

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 74d3087a72..9e5ce3163a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17645,11 +17645,11 @@ SELECT setval('myseq', 42, false);    <lineannotation>Next <function>nextval</fu
   <caution>
    <para>
     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 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</command> with
+    the same sequence, the value obtained by <function>nextval</function>
+    is not reclaimed for re-use if the calling transaction later aborts.
+    This means that transaction aborts or database crashes can result in
+    gaps in the sequence of assigned values.  That can happen without a
+    transaction abort, too.  For example an <command>INSERT</command> with
     an <literal>ON CONFLICT</literal> 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
@@ -17661,8 +17661,22 @@ SELECT setval('myseq', 42, false);    <lineannotation>Next <function>nextval</fu
    </para>

    <para>
-    Likewise, any sequence state changes made by <function>setval</function>
-    are not undone if the transaction rolls back.
+    Likewise, sequence state changes made by <function>setval</function>
+    are immediately visible to other transactions, and are not undone if
+    the calling transaction rolls back.
+   </para>
+
+   <para>
+    If the database cluster crashes before committing a transaction
+    containing a <function>nextval</function>
+    or <function>setval</function> call, the sequence state change might
+    not have made its way to persistent storage, so that it is uncertain
+    whether the sequence will have its original or updated state after the
+    cluster restarts.  This is harmless for usage of the sequence within
+    the database, since other effects of uncommitted transactions will not
+    be visible either.  However, if you wish to use a sequence value for
+    persistent outside-the-database purposes, make sure that the
+    <function>nextval</function> call has been committed before doing so.
    </para>
   </caution>


pgsql-hackers by date:

Previous
From: Jacob Champion
Date:
Subject: Re: pg_upgrade parallelism
Next
From: Tom Lane
Date:
Subject: Re: pg_upgrade parallelism