Unlogged table doesn't survive after compute node restart.

[lubennikovaav]

CREATE UNLOGGED TABLE unlog (
    id integer PRIMARY KEY,
    val integer NOT NULL
)
WITH (fillfactor='50');
 
INSERT INTO unlog (id, val)
SELECT i, 0
FROM generate_series(1, 10000) AS i;
 
VACUUM (ANALYZE) unlog;

Then stop and start a compute node and the table exists, but the file is not found:

autumn-rain-110164=> \d
               List of relations
 Schema | Name  | Type  |        Owner         
--------+-------+-------+----------------------
 public | unlog | table | lubennikovaav@zenith
(1 row)

autumn-rain-110164=> select * from unlog ;
ERROR:  could not open file "base/16385/24576": No such file or directory

autumn-rain-110164=>  
INSERT INTO unlog (id, val)
SELECT i, 0
FROM generate_series(1, 10000) AS i;
ERROR:  could not open file "base/16385/24576": No such file or directory

UPD: the behaviour is expceted and documented, what remains to be fixed here is that a user should not receive an error message, they should simply see an empty (ulogged) table instead of:

ERROR: could not open file "base/16385/24576": No such file or directory

[antons-antons]

I think this is sort of expected, as our "shutdown" is a fast one, and unlog tables don't survive such. this could be a motivation to ignore unlogged keyword and always log non-temporary tables

…

On Sat, Feb 5, 2022 at 1:02 PM lubennikovaav ***@***.***> wrote: CREATE UNLOGGED TABLE unlog ( id integer PRIMARY KEY, val integer NOT NULL ) WITH (fillfactor='50'); INSERT INTO unlog (id, val) SELECT i, 0 FROM generate_series(1, 10000) AS i; VACUUM (ANALYZE) unlog; Then stop and start a compute node and the table exists, but the file is not found: autumn-rain-110164=> \d List of relations Schema | Name | Type | Owner --------+-------+-------+---------------------- public | unlog | table | ***@***.*** (1 row) autumn-rain-110164=> select * from unlog ; ERROR: could not open file "base/16385/24576": No such file or directory autumn-rain-110164=> INSERT INTO unlog (id, val) SELECT i, 0 FROM generate_series(1, 10000) AS i; ERROR: could not open file "base/16385/24576": No such file or directory — Reply to this email directly, view it on GitHub <#1222>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AXFA2JPWKBHN3YGXRHOYDGDUZVQ3FANCNFSM5NUHYJJA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[kelvich]

+1, IMO we should bring back old behavior where we just ignored UNLOGGED

[knizhnik]

I think that Zenith users may want to have some kind of "local" tables: tables which will be stored locally at compute nodes and so provide fast access.
Certainly we can and should improve speed of retrieving pages from pageserver so that access to "normal" tables will not be significantly slower than to "local" tables. But at least now it is not true.

It is more natural to use temporary tables as local tables. But them can not be shared between backends. So unlogged tables may play this role of local tables.

Certainly current behavior is unacceptable. We should either treat each node restart as abnormal postgres termination and clear all unlogged tables.
Either really ignore UNLOGGED qualifier and treat them as normal tables.

[stepashka]

we're not sure hwo we want to support this, but we'd like the user to get to know that our behaviour is different through the troubleshooting page and an addition to our extension that @kelvich has im mind

[stepashka]

confirm that we have this documented and send to backlog @stepashka

[hlinnaka]

A user ran into this yesterday. Not sure what we want to do here, but that error is clearly not ok.

[knizhnik]

The use has replaced normal table with unlogged because he was not satisfied by speed of delete.
So if we just ignore "unlogged" qualifier, it doesn't solve the problem.
We can patch Postgres in the place where this error is produced and check if table is unlogged and if so, doesn't report error and reinitialize the table.
But... It once again is not a solution because unlogged table is removed on each node restart. Which is more similar with behavior of temporary tables and once again doesn't satisfy user's needs.

Other solutions?
Somehow maintain persistent storage at compute (for example using EBS)?

[vadim2404]

the fix is in main