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

On 2014-08-13 18:43:53 +0200, Vik Fearing wrote:
> 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.

I don't think this really is related. For one, this this surely can't be
fixed with anything checkpoint related. The overhead of that would be
prohibitive.
Other *builempty routines use smgrimmedsync(), but hash doesn't. That's
the problem here. Note that that still won't make it safe across
streaming rep et al....

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services