Hi
A few contrib modules fail to handle unlogged relations during recovery,
which leads to rather unhelpful error messages:
== primary ==
create extension pgrowlocks;
create extension pgstattuple;
create extension pg_visibility;
create extension pageinspect;
create unlogged table ut as select 1;
== standby ==
psql (20devel)
Type "help" for help.
postgres=# select * from pgrowlocks('ut');
ERROR: could not open file "base/5/16458": No such file or directory
postgres=# select * from pgstattuple('ut');
ERROR: could not open file "base/5/16458": No such file or directory
postgres=# select * from pg_visibility('ut');
ERROR: could not open file "base/5/16458": No such file or directory
postgres=# select get_raw_page('ut',0);
ERROR: could not open file "base/5/16458": No such file or directory
Attached 4 patches adding checks to report a proper error message -- one
per module for easier reviewing:
psql (20devel)
Type "help" for help.
postgres=# select * from pgrowlocks('ut');
ERROR: cannot access temporary or unlogged relations during recovery
postgres=# select * from pgstattuple('ut');
ERROR: cannot access temporary or unlogged relations during recovery
postgres=# select * from pg_visibility('ut');
ERROR: cannot access temporary or unlogged relations during recovery
postgres=# select get_raw_page('ut',0);
ERROR: cannot access temporary or unlogged relations during recovery
postgres=#
I was unable to find any TAP test I could use to add these cases, and I
believe it could be an overkill to create one just to test these error
messages, so I decided to not include any tests for now. Let me know if
I am missing something here.
These bugs were reported by Zsolt (in CC) in another related thread[1]
Thanks!
Best, Jim
1 -
https://www.postgresql.org/message-id/CAN4CZFMCuiya7d5sRQ-xm%3DhKnADiT_jrppSTo%2Ba_WxktuPk2LQ%40mail.gmail.com
From 64e2cd393a0bdbe7825ff866c76a37fd64b04f2c Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Sun, 18 Oct 2026 14:05:37 +0200
Subject: [PATCH v1 1/4] pageinspect: Reject unlogged relations during recovery
Unlogged tables and indexes have no data on a standby, so reading one
there must fail. get_raw_page(), bt_metap(), bt_page_stats(),
bt_multi_page_stats(), bt_page_items() and hash_bitmap_info() read the
relation directly instead of going through the planner, so on a standby
they failed with a confusing low-level message instead of the error
that SELECT reports:
ERROR: could not open file "base/5/16384": No such file or directory
Check for this case and report the same error as SELECT:
ERROR: cannot access temporary or unlogged relations during recovery
The init fork of an unlogged relation does exist on a standby, so
get_raw_page() can still read it.
Author: Jim Jones <[email protected]>
Reported-by: Zsolt Parragi <[email protected]>
---
contrib/pageinspect/btreefuncs.c | 13 +++++++++++++
contrib/pageinspect/hashfuncs.c | 7 +++++++
contrib/pageinspect/rawpage.c | 11 +++++++++++
3 files changed, 31 insertions(+)
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 9917663593b..676afe51de8 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -30,6 +30,7 @@
#include "access/htup_details.h"
#include "access/nbtree.h"
#include "access/relation.h"
+#include "access/xlog.h"
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
#include "catalog/pg_type.h"
@@ -241,6 +242,12 @@ bt_index_block_validate(Relation rel, int64 blkno)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
if (blkno == 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -874,6 +881,12 @@ bt_metap(PG_FUNCTION_ARGS)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
buffer = ReadBuffer(rel, 0);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
diff --git a/contrib/pageinspect/hashfuncs.c b/contrib/pageinspect/hashfuncs.c
index 30870329cc9..0f728d9add8 100644
--- a/contrib/pageinspect/hashfuncs.c
+++ b/contrib/pageinspect/hashfuncs.c
@@ -13,6 +13,7 @@
#include "access/hash.h"
#include "access/htup_details.h"
#include "access/relation.h"
+#include "access/xlog.h"
#include "catalog/pg_am.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
@@ -431,6 +432,12 @@ hash_bitmap_info(PG_FUNCTION_ARGS)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(indexRel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
if (ovflblkno < 0 || ovflblkno > MaxBlockNumber)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/contrib/pageinspect/rawpage.c b/contrib/pageinspect/rawpage.c
index d136593edb2..05ecde3c20f 100644
--- a/contrib/pageinspect/rawpage.c
+++ b/contrib/pageinspect/rawpage.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/relation.h"
+#include "access/xlog.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
@@ -175,6 +176,16 @@ get_raw_page_internal(text *relname, ForkNumber forknum, BlockNumber blkno)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /*
+ * Temporary and unlogged relations are inaccessible during recovery,
+ * except for the init fork of an unlogged relation, which does exist.
+ */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress() &&
+ forknum != INIT_FORKNUM)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
if (blkno >= RelationGetNumberOfBlocksInFork(rel, forknum))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
--
2.56.0
From bc12c677d4a2e10d6a35f540b0e0182f9b8b0545 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Sun, 18 Oct 2026 14:07:37 +0200
Subject: [PATCH v1 2/4] pgrowlocks: Reject unlogged tables during recovery
Unlogged tables have no data on a standby, so reading one there must
fail. pgrowlocks() reads the table directly instead of going through
the planner, so on a standby it failed with a confusing low-level
message instead of the error that SELECT reports:
ERROR: could not open file "base/5/16384": No such file or directory
Check for this case and report the same error as SELECT:
ERROR: cannot access temporary or unlogged relations during recovery
Author: Jim Jones <[email protected]>
Reported-by: Zsolt Parragi <[email protected]>
---
contrib/pgrowlocks/pgrowlocks.c | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/contrib/pgrowlocks/pgrowlocks.c b/contrib/pgrowlocks/pgrowlocks.c
index 87bbde59937..84017271e98 100644
--- a/contrib/pgrowlocks/pgrowlocks.c
+++ b/contrib/pgrowlocks/pgrowlocks.c
@@ -29,6 +29,7 @@
#include "access/relscan.h"
#include "access/tableam.h"
#include "access/xact.h"
+#include "access/xlog.h"
#include "catalog/namespace.h"
#include "catalog/pg_am_d.h"
#include "catalog/pg_authid.h"
@@ -114,6 +115,12 @@ pgrowlocks(PG_FUNCTION_ARGS)
aclcheck_error(aclresult, get_relkind_objtype(rel->rd_rel->relkind),
RelationGetRelationName(rel));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
/* Scan the relation */
scan = table_beginscan(rel, GetActiveSnapshot(), 0, NULL, SO_NONE);
hscan = (HeapScanDesc) scan;
--
2.56.0
From 604e1d42388e1f4be917985dbde2242c6f3858db Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Sun, 18 Oct 2026 14:09:33 +0200
Subject: [PATCH v1 3/4] pgstattuple: Reject unlogged relations during recovery
Unlogged tables and indexes have no data on a standby, so reading one
there must fail. pgstattuple(), pgstattuple_approx(), pg_relpages(),
pgstatindex(), pgstatginindex() and pgstathashindex() read the relation
directly instead of going through the planner, so on a standby they
failed with a confusing low-level message instead of the error that
SELECT reports:
ERROR: could not open file "base/5/16384": No such file or directory
Check for this case and report the same error as SELECT:
ERROR: cannot access temporary or unlogged relations during recovery
Author: Jim Jones <[email protected]>
Reported-by: Zsolt Parragi <[email protected]>
---
contrib/pgstattuple/pgstatapprox.c | 7 +++++++
contrib/pgstattuple/pgstatindex.c | 25 +++++++++++++++++++++++++
contrib/pgstattuple/pgstattuple.c | 7 +++++++
3 files changed, 39 insertions(+)
diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c
index 21e0b50fb4b..38b0cc951e2 100644
--- a/contrib/pgstattuple/pgstatapprox.c
+++ b/contrib/pgstattuple/pgstatapprox.c
@@ -16,6 +16,7 @@
#include "access/htup_details.h"
#include "access/relation.h"
#include "access/visibilitymap.h"
+#include "access/xlog.h"
#include "catalog/pg_am_d.h"
#include "commands/vacuum.h"
#include "funcapi.h"
@@ -332,6 +333,12 @@ pgstattuple_approx_internal(Oid relid, FunctionCallInfo fcinfo)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
/*
* We support only relation kinds with a visibility map and a free space
* map.
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index 8951ad0aac4..87e79527361 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -32,6 +32,7 @@
#include "access/htup_details.h"
#include "access/nbtree.h"
#include "access/relation.h"
+#include "access/xlog.h"
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
#include "funcapi.h"
@@ -238,6 +239,12 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
/*
* A !indisready index could lead to ERRCODE_DATA_CORRUPTED later, so exit
* early. We're capable of assessing an indisready&&!indisvalid index,
@@ -482,6 +489,12 @@ pg_relpages_impl(Relation rel)
RelationGetRelationName(rel)),
errdetail_relkind_not_supported(rel->rd_rel->relkind)));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
/* note: this will work OK on non-local temp tables */
relpages = RelationGetNumberOfBlocks(rel);
@@ -557,6 +570,12 @@ pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary indexes of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
/* see pgstatindex_impl */
if (!rel->rd_index->indisvalid)
ereport(ERROR,
@@ -647,6 +666,12 @@ pgstathashindex(PG_FUNCTION_ARGS)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary indexes of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
/* see pgstatindex_impl */
if (!rel->rd_index->indisvalid)
ereport(ERROR,
diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c
index 6a7f8cb4a7c..f9495390673 100644
--- a/contrib/pgstattuple/pgstattuple.c
+++ b/contrib/pgstattuple/pgstattuple.c
@@ -30,6 +30,7 @@
#include "access/nbtree.h"
#include "access/relscan.h"
#include "access/tableam.h"
+#include "access/xlog.h"
#include "catalog/namespace.h"
#include "catalog/pg_am_d.h"
#include "funcapi.h"
@@ -254,6 +255,12 @@ pgstat_relation(Relation rel, FunctionCallInfo fcinfo)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
if (RELKIND_HAS_TABLE_AM(rel->rd_rel->relkind) ||
rel->rd_rel->relkind == RELKIND_SEQUENCE)
{
--
2.56.0
From c5c4561cc4971a91b8460fe69ddb305d7a94d0a2 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Sun, 18 Oct 2026 14:10:58 +0200
Subject: [PATCH v1 4/4] pg_visibility: Reject unlogged tables during recovery
Unlogged tables have no data on a standby, so reading one there must
fail. pg_visibility(), pg_visibility_map(regclass), pg_check_frozen()
and pg_check_visible() read the table directly instead of going through
the planner, so on a standby they failed with a confusing low-level
message instead of the error that SELECT reports:
ERROR: could not open file "base/5/16384": No such file or directory
Check for this case and report the same error as SELECT:
ERROR: cannot access temporary or unlogged relations during recovery
pg_visibility_map(regclass, bigint) and pg_visibility_map_summary()
already return a result on a standby, so they are left unchanged.
Author: Jim Jones <[email protected]>
Reported-by: Zsolt Parragi <[email protected]>
---
contrib/pg_visibility/pg_visibility.c | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
diff --git a/contrib/pg_visibility/pg_visibility.c b/contrib/pg_visibility/pg_visibility.c
index 4292d077be1..054503319c6 100644
--- a/contrib/pg_visibility/pg_visibility.c
+++ b/contrib/pg_visibility/pg_visibility.c
@@ -13,6 +13,7 @@
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/visibilitymap.h"
+#include "access/xlog.h"
#include "access/xloginsert.h"
#include "catalog/pg_type.h"
#include "catalog/storage_xlog.h"
@@ -138,6 +139,12 @@ pg_visibility(PG_FUNCTION_ARGS)
/* Only some relkinds have a visibility map */
check_relation_relkind(rel);
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
if (blkno < 0 || blkno > MaxBlockNumber)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -497,6 +504,12 @@ collect_visibility_data(Oid relid, bool include_pd)
/* Only some relkinds have a visibility map */
check_relation_relkind(rel);
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
nblocks = RelationGetNumberOfBlocks(rel);
info = palloc0(offsetof(vbits, bits) + nblocks);
info->next = 0;
@@ -711,6 +724,12 @@ collect_corrupt_items(Oid relid, bool all_visible, bool all_frozen)
/* Only some relkinds have a visibility map */
check_relation_relkind(rel);
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (!RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
+
if (all_visible)
OldestXmin = GetStrictOldestNonRemovableTransactionId(rel);
--
2.56.0
