Problem Observed in behavior of Create Index Concurrently and Hot Update - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">There
seemsto be a problem in behavior of Create Index Concurrently and Hot Update in HEAD code . </span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Pleasesee the below testcase</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Step-1</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">-----------</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Client-1</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Createtable t1(c1 int, c2 int, c3 int);</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">insertinto t1 values(1,2,3);</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Step-2</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">-----------</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Client- 2 </span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">updatet1 set c2=4; where c1 = 1; -- This will be Hot
update</span><br/><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Select * from t1; </span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> c1| c2 | c3</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">----+----+----</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> 1 |  4 |  3</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">(1row)</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Noproblem till here.</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Step-3</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">-----------</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Client-1</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> createindex concurrently idx_conc_t1 on t1(c2);  -- Run this
commandin debug mode (by having breakpoint in DefineIndex)</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Stopbefore the  CommitTransactionCommand() of phase-2 where
index_buildis done and indisready flag is set to TRUE.</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Aswe have stopped before commit, still indexisready will not
bevisible to other session/transaction.</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Step-4</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">-----------</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Client-2</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">updatet1 set c2=5 where c1=1;  -- Update is success, but this
isa HOT update</span><br /><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">According to me, here is the
problem,it shouldn't have done HOT update.</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Step-5</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">-----------</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Client-1</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Resumedebugging, and complete the command. I have observed in
validate_index(),it doesn't create index entry for c2=5.</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Step-6</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">-----------</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Client-2</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> select* from t1 where c2=5;</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> c1| c2 | c3</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">----+----+----</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> 1 |  5 |  3</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">(1row)</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">postgres=#set enable_seqscan=off;          -- This is to
ensureindex scan should happen</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">SET</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">postgres=#select * from t1 where c2=5;      -- Problem, it
shouldhave shown the Row.</span><br /><span style="font-size:10.0pt;font-family:"Arial","sans-serif""> c1 | c2 |
c3</span><br/><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">----+----+----</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">(0rows)</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">postgres=#select * from t1 where c2=4;    -- Problem, query
isdone for C2=4 and the result shows  C2=5.</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> c1| c2 | c3</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">----+----+----</span><br/><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> 1 |  5 |  3</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">(1row)</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Accordingto me, the problem happens at Step-4. As at Step-4,
itdoes the HOT update due to which validate_index() is not able to put an entry for C2=5</span><br /><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Letme know if I have misunderstood something?</span><br /><br
/><br/><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">With Regards,</span><br /><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">AmitKapila.</span></div>