From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: pg_sequence_last_value() for unlogged sequences on standbys
Date: Tue, 30 Apr 2024 21:05:31 -0500
Message-ID: <20240501020531.GA721953@nathanxps13> (raw)
In-Reply-To: <20240501011317.GC594666@nathanxps13>
References: <20240501005730.GA594666@nathanxps13>
<1846474.1714525564@sss.pgh.pa.us>
<20240501011317.GC594666@nathanxps13>
On Tue, Apr 30, 2024 at 08:13:17PM -0500, Nathan Bossart wrote:
> Good point. I'll work on a patch along these lines, then.
This ended up being easier than I expected. While unlogged sequences are
only supported on v15 and above, temporary sequences have been around since
v7.2, so this will probably need to be back-patched to all supported
versions. The added test case won't work for v12-v14 since it uses an
unlogged sequence. I'm not sure it's worth constructing a test case for
temporary sequences.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.comAttachments:
[text/x-diff] v1-0001-Fix-pg_sequence_last_value-for-non-permanent-sequ.patch (2.5K, ../20240501020531.GA721953@nathanxps13/2-v1-0001-Fix-pg_sequence_last_value-for-non-permanent-sequ.patch)
download | inline diff:
From 71008f13da88f41a205e0643129162df9d2ebc81 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 30 Apr 2024 20:54:51 -0500
Subject: [PATCH v1 1/1] Fix pg_sequence_last_value() for non-permanent
sequences on standbys.
---
src/backend/commands/sequence.c | 18 +++++++++++++-----
src/test/recovery/t/001_stream_rep.pl | 8 ++++++++
2 files changed, 21 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.cindex 46103561c3..659d2ad4fc 100644--- a/src/backend/commands/sequence.c+++ b/src/backend/commands/sequence.c@@ -1780,7 +1780,7 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
Buffer buf;
HeapTupleData seqtuple;
Form_pg_sequence_data seq;
- bool is_called;+ bool is_called = false;
int64 result;
/* open and lock sequence */
@@ -1792,12 +1792,20 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
errmsg("permission denied for sequence %s",
RelationGetRelationName(seqrel))));
- seq = read_seq_tuple(seqrel, &buf, &seqtuple);+ /*+ * For the benefit of the pg_sequences system view, we return NULL for+ * temporary and unlogged sequences on standbys instead of throwing an+ * error.+ */+ if (RelationIsPermanent(seqrel) || !RecoveryInProgress())+ {+ 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);+ }
sequence_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.plindex 5311ade509..4c698b5ce1 100644--- a/src/test/recovery/t/001_stream_rep.pl+++ b/src/test/recovery/t/001_stream_rep.pl@@ -95,6 +95,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');
--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: pg_sequence_last_value() for unlogged sequences on standbys
In-Reply-To: <20240501020531.GA721953@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox