Re: Error with index on unlogged table

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On 24 March 2015 at 11:37, Andres Freund <span
dir="ltr"><<ahref="mailto:andres@anarazel.de" target="_blank">andres@anarazel.de</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><divclass=""><div class="h5">On March 24, 2015 12:35:28 PM GMT+01:00, Michael
Paquier<<a href="mailto:michael.paquier@gmail.com">michael.paquier@gmail.com</a>> wrote:<br /> >On Tue, Mar
24,2015 at 5:53 PM, Thom Brown <<a href="mailto:thom@linux.com">thom@linux.com</a>> wrote:<br /> >> I was
attemptingto set up a data set to test pg_rewind, when I<br /> >encountered<br /> >> an error.  I created a
primaryand standby, then:<br /> >><br /> >> [...]<br /> >><br /> >> # insert into utest (thing)
values('moomoo');<br /> >> ERROR:  index "utest_pkey" contains unexpected zero page at block 0<br /> >>
HINT: Please REINDEX it.<br /> >><br /> >> This is built on commit
e5f455f59fed0632371cddacddd79895b148dc07.<br/> ><br /> >Unlogged tables are not in WAL, and cannot be accessed
whilein<br /> >recovery, so having an empty index relation is expected on a promoted<br /> >standby IMO. Now
perhapswe could have a more friendly error message<br /> >in _bt_checkpage(), _hash_checkpage() and gistcheckpage()
withan<br /> >additional HINT to mention unlogged tables, but I am not sure that<br /> >this is much worth it.
Mentioningthis behavior in the docs would be<br /> >good instead.<br /><br /></div></div>I think Thom's point is
thathe promoted the node...<br /><br /> Thom, are you sure this want transient?<br /></blockquote></div><br
/></div><divclass="gmail_extra">The index is unlogged until reindexing...<br /><br /># select oid, relname,
relpersistencefrom pg_class where relname in ('test','test_pkey','utest','utest_pkey');<br />  oid  |  relname   |
relpersistence<br />-------+------------+----------------<br /> 16387 | test       | p<br /> 16394 | test_pkey  | p<br
/> 16398| utest      | u<br /> 16405 | utest_pkey | u<br />(4 rows)<br /><br /># reindex index utest_pkey;<br
/>REINDEX<br/><br /># select oid, relname, relpersistence from pg_class where relname in
('test','test_pkey','utest','utest_pkey');<br/>  oid  |  relname   | relpersistence <br
/>-------+------------+----------------<br/> 16387 | test       | p<br /> 16394 | test_pkey  | p<br /> 16398 |
utest     | u<br /> 16405 | utest_pkey | p<br />(4 rows)<br /><br /></div><div class="gmail_extra">Which is think also
raisesthe question, why are unlogged indexes made persistent by a reindex?<br clear="all" /></div><div
class="gmail_extra"><br/>-- <br /><div class="gmail_signature">Thom</div></div></div>