doc: clarify the behavior of identically-named savepoints

bmomjian · bmomjian · commit ec1fe23afa93 · 2022-07-14T15:44:22.000-04:00
Original patch by David G. Johnston. Reported-by: David G. Johnston Discussion: https://postgr.es/m/CAKFQuwYQCxSSuSL18skCWG8QHFswOJ3hjovHsOZUE346i4OpVQ@mail.gmail.com Backpatch-through: 10
diff --git a/doc/src/sgml/ref/release_savepoint.sgml b/doc/src/sgml/ref/release_savepoint.sgml
@@ -82,8 +82,9 @@ RELEASE [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
   </para>
 
   <para>
-   If multiple savepoints have the same name, only the one that was most
-   recently defined is released.
+   If multiple savepoints have the same name, only the most recently defined
+   unreleased one is released.  Repeated commands will release progressively
+   older savepoints.
   </para>
 
  </refsect1>
diff --git a/doc/src/sgml/ref/savepoint.sgml b/doc/src/sgml/ref/savepoint.sgml
@@ -53,7 +53,9 @@ SAVEPOINT <replaceable>savepoint_name</replaceable>
     <term><replaceable>savepoint_name</replaceable></term>
     <listitem>
      <para>
-      The name to give to the new savepoint.
+      The name to give to the new savepoint.  If savepoints with the
+      same name already exist, they will be inaccessible until newer
+      identically-named savepoints are released.
      </para>
     </listitem>
    </varlistentry>
@@ -106,6 +108,32 @@ COMMIT;
 </programlisting>
    The above transaction will insert both 3 and 4.
   </para>
+
+  <para>
+  To use a single savepoint name:
+<programlisting>
+BEGIN;
+    INSERT INTO table1 VALUES (1);
+    SAVEPOINT my_savepoint;
+    INSERT INTO table1 VALUES (2);
+    SAVEPOINT my_savepoint;
+    INSERT INTO table1 VALUES (3);
+
+    -- rollback to the second savepoint
+    ROLLBACK TO SAVEPOINT my_savepoint;
+    SELECT * FROM table1;               -- shows rows 1 and 2
+
+    -- release the second savepoint
+    RELEASE SAVEPOINT my_savepoint;
+
+    -- rollback to the first savepoint
+    ROLLBACK TO SAVEPOINT my_savepoint;
+    SELECT * FROM table1;               -- shows only row 1
+COMMIT;
+</programlisting>
+  The above transaction shows row 3 being rolled back first, then row 2.
+  </para>
+
  </refsect1>
 
  <refsect1>
