Date: Tue, 30 Apr 2024 19:57:30 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: pg_sequence_last_value() for unlogged sequences on standbys
Message-ID: <20240501005730.GA594666@nathanxps13>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Archived-At: 
 <https://www.postgresql.org/message-id/20240501005730.GA594666%40nathanxps13>
Precedence: bulk

If you create an unlogged sequence on a primary, pg_sequence_last_value()
for that sequence on a standby will error like so:

	postgres=# select pg_sequence_last_value('test'::regclass);
	ERROR:  could not open file "base/5/16388": No such file or directory

This function is used by the pg_sequences system view, which fails with the
same error on standbys.  The two options I see are:

* Return a better ERROR and adjust pg_sequences to avoid calling this
  function for unlogged sequences on standbys.
* Return NULL from pg_sequence_last_value() if called for an unlogged
  sequence on a standby.

As pointed out a few years ago [0], this function is undocumented, so
there's no stated contract to uphold.  I lean towards just returning NULL
because that's what we'll have to put in the relevant pg_sequences field
anyway, but I can see an argument for fixing the ERROR to align with what
you see when you try to access unlogged relations on a standby (i.e.,
"cannot access temporary or unlogged relations during recovery").

Thoughts?

[0] https://postgr.es/m/CAAaqYe8JL8Et2DoO0RRjGaMvy7-C6eDH-2wHXK-gp3dOssvBkQ%40mail.gmail.com

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com