Re: Error with index on unlogged table

On 24 March 2015 at 11:46, Thom Brown <thom(at)linux(dot)com> wrote:

>
> On 24 March 2015 at 11:37, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
>> On March 24, 2015 12:35:28 PM GMT+01:00, Michael Paquier <
>> michael(dot)paquier(at)gmail(dot)com> wrote:
>> >On Tue, Mar 24, 2015 at 5:53 PM, Thom Brown <thom(at)linux(dot)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?
>

I should also mention that it becomes unlogged again when running VACUUM
FULL or CLUSTER on the table.

--
Thom