While discussing the behavior of hash indexes with Bruce in the nearby thread , 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 , 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.  - https://www.postgresql.org/message-id/20170630005634.GA4448%40momjian.us -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Description: Binary data
-- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers