Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit857d280

Browse files
Fix pg_sequence_last_value() for unlogged sequences on standbys.
Presently, when this function is called for an unlogged sequence ona standby server, it will error out with a message likeERROR: could not open file "base/5/16388": No such file or directorySince the pg_sequences system view uses pg_sequence_last_value(),it can error similarly. To fix, modify the function to return NULLfor unlogged sequences on standby servers. Since this bug ispresent on all versions since v15, this approach is preferable tomaking the ERROR nicer because we need to repair the pg_sequencesview without modifying its definition on released versions. Forconsistency, this commit also modifies the function to return NULLfor other sessions' temporary sequences. The pg_sequences viewalready appropriately filters out such sequences, so there's no bugthere, but we might as well offer some defense in case someoneinvokes this function directly.Unlogged sequences were first introduced in v15, but temporarysequences are much older, so while the fix for unlogged sequencesis only back-patched to v15, the temporary sequence portion isback-patched to all supported versions.We could also remove the privilege check in the pg_sequences viewdefinition in v18 if we modify this function to return NULL forsequences for which the current user lacks privileges, but that isleft as a future exercise for when v18 development begins.Reviewed-by: Tom Lane, Michael PaquierDiscussion:https://postgr.es/m/20240501005730.GA594666%40nathanxps13Backpatch-through: 12
1 parent6e29963 commit857d280

File tree

3 files changed

+56
-13
lines changed

3 files changed

+56
-13
lines changed

‎doc/src/sgml/system-views.sgml

Lines changed: 25 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2927,15 +2927,36 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
29272927
<para>
29282928
The last sequence value written to disk. If caching is used,
29292929
this value can be greater than the last value handed out from the
2930-
sequence. Null if the sequence has not been read from yet. Also, if
2931-
the current user does not have <literal>USAGE</literal>
2932-
or <literal>SELECT</literal> privilege on the sequence, the value is
2933-
null.
2930+
sequence.
29342931
</para></entry>
29352932
</row>
29362933
</tbody>
29372934
</tgroup>
29382935
</table>
2936+
2937+
<para>
2938+
The <structfield>last_value</structfield> column will read as null if any of
2939+
the following are true:
2940+
<itemizedlist>
2941+
<listitem>
2942+
<para>
2943+
The sequence has not been read from yet.
2944+
</para>
2945+
</listitem>
2946+
<listitem>
2947+
<para>
2948+
The current user does not have <literal>USAGE</literal> or
2949+
<literal>SELECT</literal> privilege on the sequence.
2950+
</para>
2951+
</listitem>
2952+
<listitem>
2953+
<para>
2954+
The sequence is unlogged and the server is a standby.
2955+
</para>
2956+
</listitem>
2957+
</itemizedlist>
2958+
</para>
2959+
29392960
</sect1>
29402961

29412962
<sect1 id="view-pg-settings">

‎src/backend/commands/sequence.c

Lines changed: 22 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1810,11 +1810,8 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
18101810
Oidrelid=PG_GETARG_OID(0);
18111811
SeqTableelm;
18121812
Relationseqrel;
1813-
Bufferbuf;
1814-
HeapTupleDataseqtuple;
1815-
Form_pg_sequence_dataseq;
1816-
boolis_called;
1817-
int64result;
1813+
boolis_called= false;
1814+
int64result=0;
18181815

18191816
/* open and lock sequence */
18201817
init_sequence(relid,&elm,&seqrel);
@@ -1825,12 +1822,28 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
18251822
errmsg("permission denied for sequence %s",
18261823
RelationGetRelationName(seqrel))));
18271824

1828-
seq=read_seq_tuple(seqrel,&buf,&seqtuple);
1825+
/*
1826+
* We return NULL for other sessions' temporary sequences. The
1827+
* pg_sequences system view already filters those out, but this offers a
1828+
* defense against ERRORs in case someone invokes this function directly.
1829+
*
1830+
* Also, for the benefit of the pg_sequences view, we return NULL for
1831+
* unlogged sequences on standbys instead of throwing an error.
1832+
*/
1833+
if (!RELATION_IS_OTHER_TEMP(seqrel)&&
1834+
(RelationIsPermanent(seqrel)|| !RecoveryInProgress()))
1835+
{
1836+
Bufferbuf;
1837+
HeapTupleDataseqtuple;
1838+
Form_pg_sequence_dataseq;
1839+
1840+
seq=read_seq_tuple(seqrel,&buf,&seqtuple);
18291841

1830-
is_called=seq->is_called;
1831-
result=seq->last_value;
1842+
is_called=seq->is_called;
1843+
result=seq->last_value;
18321844

1833-
UnlockReleaseBuffer(buf);
1845+
UnlockReleaseBuffer(buf);
1846+
}
18341847
relation_close(seqrel,NoLock);
18351848

18361849
if (is_called)

‎src/test/recovery/t/001_stream_rep.pl

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,15 @@
7878
print"standby 2:$result\n";
7979
is($result,qq(33|0|t),'check streamed sequence content on standby 2');
8080

81+
# Check pg_sequence_last_value() returns NULL for unlogged sequence on standby
82+
$node_primary->safe_psql('postgres',
83+
"CREATE UNLOGGED SEQUENCE ulseq; SELECT nextval('ulseq')");
84+
$primary_lsn =$node_primary->lsn('write');
85+
$node_primary->wait_for_catchup($node_standby_1,'replay',$primary_lsn);
86+
is($node_standby_1->safe_psql('postgres',
87+
"SELECT pg_sequence_last_value('ulseq'::regclass) IS NULL"),
88+
't','pg_sequence_last_value() on unlogged sequence on standby 1');
89+
8190
# Check that only READ-only queries can run on standbys
8291
is($node_standby_1->psql('postgres','INSERT INTO tab_int VALUES (1)'),
8392
3,'read-only queries on standby 1');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp