Re: Reject access to unlogged tables in contrib modules

On Sun, 18 Oct 2026 at 19:09, Jim Jones <jim(dot)jones(at)uni-muenster(dot)de> 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