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
