Problem Observed in behavior of Create Index Concurrently and Hot Update

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.

Responses

Browse pgsql-hackers by date

  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