Hi Zsolt
On 15/10/2026 23:38, Zsolt Parragi wrote:
> Claude pointed out that a few contrib modules are also affected
> (pgstattuple, pg_visibility, pageinspect and pgrowlocks)
>
> I think the location is right for the COPY TO case, but maybe it would
> be the best to handle all locations in the same patch(set)?
The fix itself here shouldn't be too difficult, but I anticipate some
questions on "how" unlogged tables should be treated, for instance
amcheck seems to simply skips it:
postgres=# set client_min_messages to debug1;
SET
postgres=# select * from verify_heapam('ut');
DEBUG: cannot verify unlogged relation "ut" during recovery, skipping
blkno | offnum | attnum | msg
-------+--------+--------+-----
(0 rows)
So I am not sure how the mentioned contrib modules should behave. But I
agree that anything is better than the current error message:
postgres=# select * from pgrowlocks('ut');
ERROR: could not open file "base/5/16410": No such file or directory
That said, I would be in favour of creating a separate patch for the
contrib modules. WDYT?
>
> + foreach_oid(child, children)
> + {
> + if (get_rel_persistence(child) !=
> RELPERSISTENCE_PERMANENT)
>
> Can we put this check in the existing loop above? And maybe the error
> detail could also name the specific relation?
I moved the check to the existing loop and added the relation name to
errdetail on partition tables:
== primary ==
psql (20devel)
Type "help" for help.
postgres=# create unlogged table ut as select 1 ;
SELECT 1
postgres=# create table pt (a int) partition by range (a);
CREATE TABLE
postgres=# create unlogged table pt_p1 partition of pt for values from
(0) to (10);
CREATE TABLE
== standby ==
psql (20devel)
Type "help" for help.
postgres=# copy ut to stdout;
ERROR: cannot access temporary or unlogged relations during recovery
postgres=# copy pt to stdout;
ERROR: cannot access temporary or unlogged relations during recovery
DETAIL: Partition "pt_p1" is an unlogged table in partitioned table "pt".
I didn't add the relation name to non-partitioned unlogged tables to
make it consistent with the errmsg of a select:
postgres=# select * from ut;
ERROR: cannot access temporary or unlogged relations during recovery
PFA v2.
Thanks for the review!
Best, Jim
From 9e5d9cfaf0b24dc99a7a5a6b3c26ad426f51676d Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Fri, 16 Oct 2026 09:12:23 +0200
Subject: [PATCH v2] Reject COPY TO of unlogged tables during recovery
Unlogged tables have no data on a standby, so reading one there must
fail. SELECT and COPY (SELECT ...) TO already report a clear error,
but the table form of COPY TO reads the table directly and fails with
a confusing low-level message instead:
ERROR: could not open file "base/5/16384": No such file or directory
Check for this case in COPY TO and report a proper error. When copying
from a partitioned table, also check each partition, so that an
unlogged partition is also reported.
Author: Jim Jones <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/b6d71815-d362-4579-bf07-73eca01aee6d%40uni-muenster.de
---
src/backend/commands/copyto.c | 19 +++++++++++++++++++
src/test/recovery/t/001_stream_rep.pl | 27 +++++++++++++++++++++++++++
2 files changed, 46 insertions(+)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index a187d8ec284..9ca06ed3e18 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -21,6 +21,7 @@
#include "access/table.h"
#include "access/tableam.h"
#include "access/tupconvert.h"
+#include "access/xlog.h"
#include "catalog/pg_inherits.h"
#include "commands/copyapi.h"
#include "commands/progress.h"
@@ -877,6 +878,19 @@ BeginCopyTo(ParseState *pstate,
errhint("Try the COPY (SELECT ...) TO variant."));
}
+ /* Unlogged partitions are inaccessible during recovery. */
+ if (RecoveryInProgress() &&
+ get_rel_persistence(child) == RELPERSISTENCE_UNLOGGED)
+ {
+ char *relation_name = get_rel_name(child);
+
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery"),
+ errdetail("Partition \"%s\" is an unlogged table in partitioned table \"%s\".",
+ relation_name, RelationGetRelationName(rel)));
+ }
+
/* Exclude tables with no data */
if (RELKIND_HAS_PARTITIONS(relkind))
children = foreach_delete_current(children, child);
@@ -889,6 +903,11 @@ BeginCopyTo(ParseState *pstate,
RelationGetRelationName(rel))));
}
+ /* Temporary and unlogged relations are inaccessible during recovery. */
+ if (rel != NULL && !RelationIsPermanent(rel) && RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary or unlogged relations during recovery")));
/* Allocate workspace and zero all fields */
cstate = palloc0_object(CopyToStateData);
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index a4fa4b96c61..528ef853ca7 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -113,6 +113,33 @@ is( $node_standby_1->safe_psql(
't',
'pg_sequence_last_value() on unlogged sequence on standby 1');
+# Check that COPY TO rejects unlogged tables on standby, including when
+# reached through a partitioned table, but still copies permanent ones
+$node_primary->safe_psql(
+ 'postgres', q{
+CREATE UNLOGGED TABLE ultab (a int);
+CREATE TABLE ulpart (a int) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE ulpart_1 PARTITION OF ulpart FOR VALUES FROM (0) TO (10);
+CREATE TABLE lpart (a int) PARTITION BY RANGE (a);
+CREATE TABLE lpart_1 PARTITION OF lpart FOR VALUES FROM (0) TO (10);
+INSERT INTO lpart VALUES (1);
+});
+$node_primary->wait_for_replay_catchup($node_standby_1);
+my ($copy_ret, $copy_stdout, $copy_stderr) =
+ $node_standby_1->psql('postgres', 'COPY ultab TO STDOUT');
+like(
+ $copy_stderr,
+ qr/cannot access temporary or unlogged relations during recovery/,
+ 'COPY TO of unlogged table on standby 1');
+($copy_ret, $copy_stdout, $copy_stderr) =
+ $node_standby_1->psql('postgres', 'COPY ulpart TO STDOUT');
+like(
+ $copy_stderr,
+ qr/cannot access temporary or unlogged relations during recovery.*\n.*DETAIL: Partition "ulpart_1" is an unlogged table in partitioned table "ulpart"/,
+ 'COPY TO of partitioned table with unlogged partition on standby 1');
+is($node_standby_1->safe_psql('postgres', 'COPY lpart TO STDOUT'),
+ '1', 'COPY TO of partitioned table 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.56.0
