Hi While testing the unlogged materialized views patch I noticed that copy with an unlogged table from a standby tries to read a file that does not exist on disk:
== primary == psql (20devel) Type "help" for help. postgres=# create unlogged table ut as select 1; SELECT 1 == standby == psql (20devel) Type "help" for help. postgres=# copy ut to stdout; ERROR: could not open file "base/5/16384": No such file or directory I could reproduce this bug on PG14 as well: == primary == psql (14.23 (Debian 14.23-1.pgdg13+1)) Geben Sie »help« für Hilfe ein. postgres=# create unlogged table ut1 as select 1; SELECT 1 == standby == psql (14.23 (Debian 14.23-1.pgdg13+1)) Geben Sie »help« für Hilfe ein. postgres=# copy ut1 to stdout; ERROR: could not open file "base/13759/772691051": No such file or directory I did some digging and found this thread[1] fixing a similar problem in pg_dump, but I couldn't find anything similar on the server. I managed to get a proper error message by adding a check in copyto.c (patch attached), but I'm not sure this is the right place for it. psql (20devel) Type "help" for help. postgres=# copy ut to stdout; ERROR: cannot access temporary or unlogged relations during recovery Before I put too much effort on tests I would like to hear your thoughts on the approach. Best, Jim 1 - https://www.postgresql.org/message-id/flat/E1TutDO-0001Hb-2v%40wrigleys.postgresql.org
From d31e7b80bf0368f42e94767a0dbf3570af72c88b Mon Sep 17 00:00:00 2001 From: Jim Jones <[email protected]> Date: Thu, 15 Oct 2026 20:35:03 +0200 Subject: [PATCH v1] Reject COPY TO of unlogged relations during recovery --- src/backend/commands/copyto.c | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c index a187d8ec284..18a87efe2e9 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" @@ -889,6 +890,25 @@ BeginCopyTo(ParseState *pstate, RelationGetRelationName(rel)))); } + /* Temporary and unlogged relations are inaccessible during recovery. */ + if (rel != NULL && RecoveryInProgress()) + { + bool accessible = RelationIsPermanent(rel); + + foreach_oid(child, children) + { + if (get_rel_persistence(child) != RELPERSISTENCE_PERMANENT) + { + accessible = false; + break; + } + } + + if (!accessible) + 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); -- 2.56.0
