Fix pg_sequence_last_value() for unlogged sequences on standbys.

Presently, when this function is called for an unlogged sequence on
a standby server, it will error out with a message like

	ERROR:  could not open file "base/5/16388": No such file or directory

Since the pg_sequences system view uses pg_sequence_last_value(),
it can error similarly.  To fix, modify the function to return NULL
for unlogged sequences on standby servers.  Since this bug is
present on all versions since v15, this approach is preferable to
making the ERROR nicer because we need to repair the pg_sequences
view without modifying its definition on released versions.  For
consistency, this commit also modifies the function to return NULL
for other sessions' temporary sequences.  The pg_sequences view
already appropriately filters out such sequences, so there's no bug
there, but we might as well offer some defense in case someone
invokes this function directly.

Unlogged sequences were first introduced in v15, but temporary
sequences are much older, so while the fix for unlogged sequences
is only back-patched to v15, the temporary sequence portion is
back-patched to all supported versions.

We could also remove the privilege check in the pg_sequences view
definition in v18 if we modify this function to return NULL for
sequences for which the current user lacks privileges, but that is
left as a future exercise for when v18 development begins.

Reviewed-by: Tom Lane, Michael Paquier
Discussion: https://postgr.es/m/20240501005730.GA594666%40nathanxps13
Backpatch-through: 12

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 39815d5faf627b214d04d8d776ae4aea0995ed39..dc709ec9f50669507ac02741e4a734d5c4500f4e 100644 (file)
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -3009,15 +3009,36 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
       <para>
        The last sequence value written to disk.  If caching is used,
        this value can be greater than the last value handed out from the
-       sequence.  Null if the sequence has not been read from yet.  Also, if
-       the current user does not have <literal>USAGE</literal>
-       or <literal>SELECT</literal> privilege on the sequence, the value is
-       null.
+       sequence.
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>
+
+  <para>
+   The <structfield>last_value</structfield> column will read as null if any of
+   the following are true:
+   <itemizedlist>
+    <listitem>
+     <para>
+      The sequence has not been read from yet.
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      The current user does not have <literal>USAGE</literal> or
+      <literal>SELECT</literal> privilege on the sequence.
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      The sequence is unlogged and the server is a standby.
+     </para>
+    </listitem>
+   </itemizedlist>
+  </para>
+
  </sect1>
 
  <sect1 id="view-pg-settings">

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index c7e262c0fcc5f4a86bb218a8a0335e89531754f1..3fa4e7885776808d9fb20bae3810181ebf70b6ca 100644 (file)
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1795,11 +1795,8 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
    Oid         relid = PG_GETARG_OID(0);
    SeqTable    elm;
    Relation    seqrel;
-   Buffer      buf;
-   HeapTupleData seqtuple;
-   Form_pg_sequence_data seq;
-   bool        is_called;
-   int64       result;
+   bool        is_called = false;
+   int64       result = 0;
 
    /* open and lock sequence */
    init_sequence(relid, &elm, &seqrel);
@@ -1810,12 +1807,28 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
                 errmsg("permission denied for sequence %s",
                        RelationGetRelationName(seqrel))));
 
-   seq = read_seq_tuple(seqrel, &buf, &seqtuple);
+   /*
+    * We return NULL for other sessions' temporary sequences.  The
+    * pg_sequences system view already filters those out, but this offers a
+    * defense against ERRORs in case someone invokes this function directly.
+    *
+    * Also, for the benefit of the pg_sequences view, we return NULL for
+    * unlogged sequences on standbys instead of throwing an error.
+    */
+   if (!RELATION_IS_OTHER_TEMP(seqrel) &&
+       (RelationIsPermanent(seqrel) || !RecoveryInProgress()))
+   {
+       Buffer      buf;
+       HeapTupleData seqtuple;
+       Form_pg_sequence_data seq;
+
+       seq = read_seq_tuple(seqrel, &buf, &seqtuple);
 
-   is_called = seq->is_called;
-   result = seq->last_value;
+       is_called = seq->is_called;
+       result = seq->last_value;
 
-   UnlockReleaseBuffer(buf);
+       UnlockReleaseBuffer(buf);
+   }
    relation_close(seqrel, NoLock);
 
    if (is_called)

diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 0c72ba094415c5e2d8e3e1287eb814a2f5b4d634..710bdd54dabe769ac8fc7a59a44906f5d558b784 100644 (file)
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -76,6 +76,14 @@ $result = $node_standby_2->safe_psql('postgres', "SELECT * FROM seq1");
 print "standby 2: $result\n";
 is($result, qq(33|0|t), 'check streamed sequence content on standby 2');
 
+# Check pg_sequence_last_value() returns NULL for unlogged sequence on standby
+$node_primary->safe_psql('postgres',
+   "CREATE UNLOGGED SEQUENCE ulseq; SELECT nextval('ulseq')");
+$node_primary->wait_for_replay_catchup($node_standby_1);
+is($node_standby_1->safe_psql('postgres',
+   "SELECT pg_sequence_last_value('ulseq'::regclass) IS NULL"),
+   't', 'pg_sequence_last_value() on unlogged sequence on standby 1');
+
 # Check that only READ-only queries can run on standbys
 is($node_standby_1->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
    3, 'read-only queries on standby 1');