On Sun, 18 Oct 2026 at 19:09, Jim Jones <[email protected]> wrote: > > 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
Hi!
I checked v1 patchset, I think it's good. There is however one more
case to cover in pagenspect: TOAST pointers. We can try to check page
content on standby where content can refer to unlogged TOAST. In
reproducer I simply save unlogged heap page to logged relation:
Setup:
CREATE EXTENSION pageinspect;
CREATE UNLOGGED TABLE u_toast (b text);
ALTER TABLE u_toast ALTER COLUMN b SET STORAGE EXTERNAL;
INSERT INTO u_toast VALUES (repeat('abc', 3000));
CREATE TABLE u_snapshot AS
SELECT get_raw_page('u_toast', 0) AS raw;
Then on standby:
reshke=# table u_toast;
ERROR: cannot access temporary or unlogged relations during recovery
reshke=# SELECT octet_length(t_attrs[1])
FROM u_snapshot,
LATERAL heap_page_item_attrs(raw, 'u_toast'::regclass, true);
ERROR: could not open file "base/16384/16393": No such file or directory
CONTEXT: SQL function "heap_page_item_attrs" statement 1
attached fixes an issue.
Also, Im +0.5 on not creating a TAP test for that -- too many cpu
cycles for too little benefit.
--
Best regards,
Kirill Reshke
fix-t204-cf7478-0001-pageinspect-guard-unlogged-detoast.patch
Description: Binary data
