Re: Truncation of UNLOGGED tables upon restart.

[Ron <ronljohnsonjr@xxxxxxxxx>]

On 11/01/2018 03:58 PM, David G. Johnston wrote:

On Thu, Nov 1, 2018 at 1:49 PM Ravi Krishna <srkrishna1@xxxxxxx> wrote:

Per documentation unlogged tables are not crash safe and PG will truncate it when it restarts after a crash. Does this apply to even read only unlogged tables.

For example:

On Monday I load data into unlogged tables.

Then from Tue onwards the table is only read by application.

On Fri morning PG crashes.  When it restarts will it truncate all unlogged tables even though they remained
unchanged.  I hope it does not.

"an unlogged table is automatically truncated after a crash or unclean shutdown." - there are no exceptions noted.

There is no such thing as a "read only" table in PostgreSQL.  All tables are read/write no matter that frequency of either event.  There is nothing inherently special about "no writes for 4 days" and "no writes for 10 seconds" that would allow for a distinction to be made.  There could be write in progress on the table just as it crashes Friday.

Too bad you can't say ALTER TABLESPACE foo SET READONLY = false;

That would effectively give you read only tables.  I've done that before on the legacy RDBMS I managed.

--
Angular momentum makes the world go 'round.