From: | Amit Kapila <amit(dot)kapila(at)huawei(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Problem Observed in behavior of Create Index Concurrently and Hot Update |
Date: | 2012-10-31 06:11:37 |
Message-ID: | 006801cdb72e$96b62330$c4226990$@kapila@huawei.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
There seems to be a problem in behavior of Create Index Concurrently and Hot
Update in HEAD code .
Please see the below testcase
Step-1
-----------
Client-1
Create table t1(c1 int, c2 int, c3 int);
insert into t1 values(1,2,3);
Step-2
-----------
Client - 2
update t1 set c2=4; where c1 = 1; -- This will be Hot update
Select * from t1;
c1 | c2 | c3
----+----+----
1 | 4 | 3
(1 row)
No problem till here.
Step-3
-----------
Client -1
create index concurrently idx_conc_t1 on t1(c2); -- Run this command in
debug mode (by having breakpoint in DefineIndex)
Stop before the CommitTransactionCommand() of phase-2 where index_build is
done and indisready flag is set to TRUE.
As we have stopped before commit, still indexisready will not be visible to
other session/transaction.
Step-4
-----------
Client -2
update t1 set c2=5 where c1=1; -- Update is success, but this is a HOT
update
According to me, here is the problem, it shouldn't have done HOT update.
Step-5
-----------
Client-1
Resume debugging, and complete the command. I have observed in
validate_index(), it doesn't create index entry for c2=5.
Step-6
-----------
Client-2
select * from t1 where c2=5;
c1 | c2 | c3
----+----+----
1 | 5 | 3
(1 row)
postgres=# set enable_seqscan=off; -- This is to ensure index scan
should happen
SET
postgres=# select * from t1 where c2=5; -- Problem, it should have
shown the Row.
c1 | c2 | c3
----+----+----
(0 rows)
postgres=# select * from t1 where c2=4; -- Problem, query is done for
C2=4 and the result shows C2=5.
c1 | c2 | c3
----+----+----
1 | 5 | 3
(1 row)
According to me, the problem happens at Step-4. As at Step-4, it does the
HOT update due to which validate_index() is not able to put an entry for
C2=5
Let me know if I have misunderstood something?
With Regards,
Amit Kapila.
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2012-10-31 08:36:45 | Re: Limiting the number of parameterized indexpaths created |
Previous Message | Amit Kapila | 2012-10-31 04:02:35 | Re: Proposal for Allow postgresql.conf values to be changed via SQL |