Re: [HACKERS] hash index on unlogged tables doesn't behave asexpected - Mailing list pgsql-hackers

Hello,

Following a bit older thread.

At Tue, 18 Jul 2017 08:33:07 +0200, Michael Paquier <michael.paquier@gmail.com> wrote in
<CAB7nPqSQDmz+PKewNN9r_7jC4WKf9f31Gkf=DzVGA3q+GsgJEQ@mail.gmail.com>
> On Tue, Jul 18, 2017 at 4:18 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > Thanks.  Do you have any suggestion for back-branches?  As of now, it
> > fails badly with below kind of error:
> >
> > test=> SELECT * FROM t_u_hash;
> > ERROR:  could not open file "base/16384/16392": No such file or directory
> >
> > It is explained in another thread [3] where it has been found that the
> > reason for such an error is that hash indexes are not WAL logged prior
> > to 10.  Now, we can claim that we don't recommend hash indexes to be
> > used prior to 10 in production, so such an error is okay even if there
> > is no crash has happened in the system.
> 
> There are a couple of approaches:
> 1) Marking such indexes as invalid at recovery and log information
> about the switch done.
> 2) Error at creation of hash indexes on unlogged tables.
> 3) Leave it as-is, because there is already a WARNING at creation.
> I don't mind seeing 3) per the amount of work done lately to support
> WAL on hash indexes.

I overlooked that but (3) is true as long as the table is
*logged* one.

postgres=# create table test (id int primary key, v text);
postgres=# create index on test using hash (id);
WARNING:  hash indexes are not WAL-logged and their use is discouraged

But not for for unlogged tables.

postgres=# create unlogged table test (id int primary key, v text);
postgres=# create index on test using hash (id);
postgres=# (no warning)

And fails on promotion in the same way.

postgres=# select * from test;
ERROR:  could not open file "base/13324/16446": No such file or directory

indexcmds.c@965:503
>   if (strcmp(accessMethodName, "hash") == 0 &&
>     RelationNeedsWAL(rel))
>     ereport(WARNING,
>         (errmsg("hash indexes are not WAL-logged and their use is discouraged")));

Using !RelationUsesLocalBuffers instead fixes that and the
attached patch is for 9.6. I'm a bit unconfident on the usage of
logical meaning of the macro but what it does fits there.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
***************
*** 501,507 **** DefineIndex(Oid relationId,     amRoutine = GetIndexAmRoutine(accessMethodForm->amhandler);      if
(strcmp(accessMethodName,"hash") == 0 &&
 
!         RelationNeedsWAL(rel))         ereport(WARNING,                 (errmsg("hash indexes are not WAL-logged and
theiruse is discouraged"))); 
 
--- 501,507 ----     amRoutine = GetIndexAmRoutine(accessMethodForm->amhandler);      if (strcmp(accessMethodName,
"hash")== 0 &&
 
!         !RelationUsesLocalBuffers(rel))         ereport(WARNING,                 (errmsg("hash indexes are not
WAL-loggedand their use is discouraged")));  

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers