Re: Error with index on unlogged table

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?

--
Thom