Re: BUG #10675: alter database set tablespace and unlogged table

On 08/13/2014 04:32 PM, Andres Freund wrote:
> On 2014-08-13 23:31:46 +0900, MauMau wrote:
>> From: "Andres Freund" <andres(at)2ndquadrant(dot)com>
>>> That'd mean that the next pointrelease will incur *significantly* higher
>>> IO in many setups. If you currently have a workload where all dirty
>>> buffers of unlogged tables fit in s_b you'll never have any OS
>>> writes. That'd completely change.
>>
>> Yes, that's the only headache... But I'm not worried so much, because
>> bgwriter and/or backends may write out dirty buffers for unlogged tables, so
>> the total I/O is not low anyway.
>
> If the workload fits in s_b - not an infrequent thing with today's
> memory sizes - that's simply not true.
>
>>>> * There's a greater danger of losing data during operating system
>>>> restart.
>>>> For example, IIRC, Windows gives only 20 seconds to terminate all
>>>> services
>>>> during OS shutdown. If many dirty buffers for unlogged tables linger in
>>>> the
>>>> shared buffers, PostgreSQL service may fail to complete database
>>>> shutdown.
>>>> Even if the online checkpoint writes out all dirty buffers, the
>>>> possibility
>>>> of there being many dirty buffers at shutdown is not zero, but the
>>>> probability would be lower.
>>>
>>> Meh. That won't lead to data loss, just recovery on restart. And 20s
>>> isn't sufficient for any halfway busy database anyway.
>>
>> The unlogged tables are emptied (= data loss) at recovery restart.
>
> If that's data loss you shouldn't be using unlogged tables. That's not
> an argument.

There is also this issue which has been bugging me for a while but I
haven't had time to look at providing a patch for:

postgres=# create unlogged table t (id integer);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# create index on t using hash (id);
CREATE INDEX

<crash and restart server here>

postgres=# set enable_seqscan = off;
SET
postgres=# select * from t where id = 1;
ERROR: index "t_id_idx" contains unexpected zero page at block 0
HINT: Please REINDEX it.

All because the init fork is never checkpointed to disk. If there's
anywhere a hash index should be safe to use, it's on unlogged tables.
--
Vik