While discussing the behavior of hash indexes with Bruce in the nearby
thread [1], it has been noticed that hash index on unlogged tables
doesn't behave as expected. Prior to 10, it has the different set of
problems (mainly because hash indexes are not WAL-logged) which were
discussed on that thread [1], however when I checked, it doesn't work
even for 10. Below are steps to reproduce the problem.
1. Setup master and standby
2. On the master, create unlogged table and hash index.
2A. Create unlogged table t1(c1 int);
2B. Create hash index idx_t1_hash on t1 using hash(c1);
3. On Standby, try selecting data,
select * from t1;
ERROR: cannot access temporary or unlogged relations during recovery
---Till here everything works as expected
4. Promote standby to master (I have just stopped the standby and
master and removed recovery.conf file from the standby database
location) and try starting the new master, it
gives below error and doesn't get started.
FATAL: could not create file "base/12700/16387": File exists
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.
I think this should be considered a PostgreSQL 10 open item.
[1] - https://www.postgresql.org/message-id/20170630005634.GA4448%40momjian.us
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers