On Fri, Jun 30, 2017 at 6:26 AM, Bruce Momjian <br...@momjian.us> wrote: > On Wed, Jun 28, 2017 at 10:11:35PM -0400, Bruce Momjian wrote: >> On Fri, Jun 23, 2017 at 06:17:47PM +0300, Sergey Burladyan wrote: >> > PS: >> > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-) >> > >> > It is about hash index and promote: >> > 1. create master >> > 2. create standby from it >> > 3. create unlogged table and hash index like: >> > create unlogged table test (id int primary key, v text); >> > create index on test using hash (id); >> > 3. stop master >> > 4. promote standby >> > >> > now, if you try to upgrade this new promoted master pg_upgrade will stop >> > on this hash index: >> > error while creating link for relation "public.test_id_idx" >> > ("s/9.2/base/16384/16393" to "m/9.4/base/16422/16393"): No such file or >> > directory >> > Failure, exiting >> > >> > I touch this file (s/9.2/base/16384/16393) and rerun pg_upgrade from >> > scratch and it complete successfully. >> >> Sergey, can you please test if the table "test" is not unlogged, does >> pg_upgrade still fail on the hash index file? > > I was able to reproduce this failure on my server. :-) > > What I found is that the problem is larger than I thought. Sergey is > correct that pg_upgrade fails because there is no hash file associated > with the unlogged table, but in fact a simple access of the unlogged > table with a hash index generates an error: > > test=> SELECT * FROM t_u_hash; > ERROR: could not open file "base/16384/16392": No such file or > directory > > What is interesting is that this is the only combination that generates > an error. >
Yes and that is because normally we log the creation of init fork for unlogged relations (both heap and index, refer btbuildempty for index and heap_create_init_fork for heap), but for hash indexes prior to 10, we don't log for init forks. > A unlogged able with a btree index or a logged table with a > hash index are fine, e.g.: > > List of relations > Schema | Name | Type | Owner > --------+-----------+-------+---------- > public | t_btree | table | postgres > public | t_hash | table | postgres > public | t_u_btree | table | postgres > fail--> public | t_u_hash | table | postgres > > This doesn't fail on PG 10 since we WAL-log hash indexes. > > I think we have two questions: > > 1. do we fix this in the server If we want to fix this in the server then we need to log (write WAL) the init fork for hash indexes. > 2. if not, do we fix pg_upgrade > I think even if we provide a fix in pg_upgrade, it might not suffice the need because this problem can come if the user just promotes standby server (<=9.6) to master considering we had unlogged table and hash index on that table. I think we should fix the server. -- 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