Make TRUNCATE ... RESTART IDENTITY restart sequences transactionally.

In the previous coding, we simply issued ALTER SEQUENCE RESTART commands,
which do not roll back on error.  This meant that an error between
truncating and committing left the sequences out of sync with the table
contents, with potentially bad consequences as were noted in a Warning on
the TRUNCATE man page.

To fix, create a new storage file (relfilenode) for a sequence that is to
be reset due to RESTART IDENTITY.  If the transaction aborts, we'll
automatically revert to the old storage file.  This acts just like a
rewriting ALTER TABLE operation.  A penalty is that we have to take
exclusive lock on the sequence, but since we've already got exclusive lock
on its owning table, that seems unlikely to be much of a problem.

The interaction of this with usual nontransactional behaviors of sequence
operations is a bit weird, but it's hard to see what would be completely
consistent.  Our choice is to discard cached-but-unissued sequence values
both when the RESTART is executed, and at rollback if any; but to not touch
the currval() state either time.

In passing, move the sequence reset operations to happen before not after
any AFTER TRUNCATE triggers are fired.  The previous ordering was not
logically sensible, but was forced by the need to minimize inconsistency
if the triggers caused an error.  Transactional rollback is a much better
solution to that.

Patch by Steve Singer, rather heavily adjusted by me.

1 files changed, 28 insertions, 35 deletions

diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml
index f32d255c74b..9f12ca4b3b3 100644
--- a/doc/src/sgml/ref/truncate.sgml
+++ b/doc/src/sgml/ref/truncate.sgml
@@ -108,7 +108,9 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
   <para>
    <command>TRUNCATE</> acquires an <literal>ACCESS EXCLUSIVE</> lock on each
    table it operates on, which blocks all other concurrent operations
-   on the table.  If concurrent access to a table is required, then
+   on the table.  When <literal>RESTART IDENTITY</> is specified, any
+   sequences that are to be restarted are likewise locked exclusively.
+   If concurrent access to a table is required, then
    the <command>DELETE</> command should be used instead.
   </para>
 
@@ -130,7 +132,8 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
    the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
    fired before any truncation happens, and all <literal>AFTER
    TRUNCATE</literal> triggers are fired after the last truncation is
-   performed.  The triggers will fire in the order that the tables are
+   performed and any sequences are reset.
+   The triggers will fire in the order that the tables are
    to be processed (first those listed in the command, and then any
    that were added due to cascading).
   </para>
@@ -159,32 +162,21 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
    transaction does not commit.
   </para>
 
-  <warning>
-   <para>
-    Any <command>ALTER SEQUENCE RESTART</> operations performed as a
-    consequence of using the <literal>RESTART IDENTITY</> option are
-    nontransactional and will not be rolled back on failure.  To minimize
-    the risk, these operations are performed only after all the rest of
-    <command>TRUNCATE</>'s work is done.  However, there is still a risk
-    if <command>TRUNCATE</> is performed inside a transaction block that is
-    aborted afterwards.  For example, consider
-
-<programlisting>
-BEGIN;
-TRUNCATE TABLE foo RESTART IDENTITY;
-COPY foo FROM ...;
-COMMIT;
-</programlisting>
-
-    If the <command>COPY</> fails partway through, the table data
-    rolls back correctly, but the sequences will be left with values
-    that are probably smaller than they had before, possibly leading
-    to duplicate-key failures or other problems in later transactions.
-    If this is likely to be a problem, it's best to avoid using
-    <literal>RESTART IDENTITY</>, and accept that the new contents of
-    the table will have higher serial numbers than the old.
-   </para>
-  </warning>
+  <para>
+   When <literal>RESTART IDENTITY</> is specified, the implied
+   <command>ALTER SEQUENCE RESTART</> operations are also done
+   transactionally; that is, they will be rolled back if the surrounding
+   transaction does not commit.  This is unlike the normal behavior of
+   <command>ALTER SEQUENCE RESTART</>.  Be aware that if any additional
+   sequence operations are done on the restarted sequences before the
+   transaction rolls back, the effects of these operations on the sequences
+   will be rolled back, but not their effects on <function>currval()</>;
+   that is, after the transaction <function>currval()</> will continue to
+   reflect the last sequence value obtained inside the failed transaction,
+   even though the sequence itself may no longer be consistent with that.
+   This is similar to the usual behavior of <function>currval()</> after
+   a failed transaction.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -222,13 +214,14 @@ TRUNCATE othertable CASCADE;
   <title>Compatibility</title>
 
   <para>
-   The SQL:2008 standard includes a <command>TRUNCATE</command> command with the syntax
-   <literal>TRUNCATE TABLE <replaceable>tablename</replaceable></literal>.
-   The clauses <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
-   also appear in that standard but have slightly different but related meanings.
-   Some of the concurrency behavior of this command is left implementation-defined
-   by the standard, so the above notes should be considered and compared with
-   other implementations if necessary.
+   The SQL:2008 standard includes a <command>TRUNCATE</command> command
+   with the syntax <literal>TRUNCATE TABLE
+   <replaceable>tablename</replaceable></literal>.  The clauses
+   <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
+   also appear in that standard, but have slightly different though related
+   meanings.  Some of the concurrency behavior of this command is left
+   implementation-defined by the standard, so the above notes should be
+   considered and compared with other implementations if necessary.
   </para>
  </refsect1>
 </refentry>