Re: hash index on unlogged tables doesn't behave as expected

On Mon, Jul 3, 2017 at 3:24 PM, Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> On Mon, Jul 3, 2017 at 9:12 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> The basic issue was that the WAL logging for Create Index operation
>> was oblivion of the fact that for unlogged tables only INIT forks need
>> to be logged. Another point which we need to consider is that while
>> replaying the WAL for the create index operation, we need to flush the
>> buffer if it is for init fork. This needs to be done only for pages
>> that can be part of init fork file (like metapage, bitmappage).
>> Attached patch fixes the issue.
>>
>> Another approach to fix the issue could be that always log complete
>> pages for the create index operation on unlogged tables (in
>> hashbuildempty). However, the logic for initial hash index pages
>> needs us to perform certain other actions (like update metapage after
>> the creation of bitmappage) which make it difficult to follow that
>> approach.
>>
>
> Thanks for sharing the steps to reproduce the issue in detail. I could
> easily reproduce this issue. I also had a quick look into the patch and the
> fix looks fine to me. However, it would be good if we can add at least one
> test for unlogged table with hash index in the regression test suite.
>

There is already such a test, see create_index.sql.
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id
int4_ops);

Do you have something else in mind?

I think the problem mentioned in this thread can be caught only if we
promote the standby and restart it. We might be able to write it
using TAP framework, but I think such a test should exist for other
index types as well or in general for unlogged tables. I am not
opposed to writing such a test if you and or others feel so.

> Apart from that i have tested the patch and the patch seems to be working
> fine.

Thanks.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com