Re: Error with index on unlogged table

On 24 March 2015 at 11:37, Andres Freund <andres@anarazel.de> wrote:

On March 24, 2015 12:35:28 PM GMT+01:00, Michael Paquier <michael.paquier@gmail.com> wrote:
>On Tue, Mar 24, 2015 at 5:53 PM, Thom Brown <thom@linux.com> wrote:
>> I was attempting to set up a data set to test pg_rewind, when I
>encountered
>> an error.  I created a primary and standby, then:
>>
>> [...]
>>
>> # insert into utest (thing) values ('moomoo');
>> ERROR:  index "utest_pkey" contains unexpected zero page at block 0
>> HINT:  Please REINDEX it.
>>
>> This is built on commit e5f455f59fed0632371cddacddd79895b148dc07.
>
>Unlogged tables are not in WAL, and cannot be accessed while in
>recovery, so having an empty index relation is expected on a promoted
>standby IMO. Now perhaps we could have a more friendly error message
>in _bt_checkpage(), _hash_checkpage() and gistcheckpage() with an
>additional HINT to mention unlogged tables, but I am not sure that
>this is much worth it. Mentioning this behavior in the docs would be
>good instead.

I think Thom's point is that he promoted the node...

Thom, are you sure this want transient?

The index is unlogged until reindexing...

# select oid, relname, relpersistence from pg_class where relname in ('test','test_pkey','utest','utest_pkey');
  oid  |  relname   | relpersistence
-------+------------+----------------
 16387 | test       | p
 16394 | test_pkey  | p
 16398 | utest      | u
 16405 | utest_pkey | u
(4 rows)

# reindex index utest_pkey;
REINDEX

# select oid, relname, relpersistence from pg_class where relname in ('test','test_pkey','utest','utest_pkey');
  oid  |  relname   | relpersistence
-------+------------+----------------
 16387 | test       | p
 16394 | test_pkey  | p
 16398 | utest      | u
 16405 | utest_pkey | p
(4 rows)

Which is think also raises the question, why are unlogged indexes made persistent by a reindex?