Re: Predicate locking - Mailing list pgsql-hackers

Kevin Grittner wrote:
> Check where the plan goes from a table scan to an indexed access.
> Also look at what is showing for SIRead locks in pg_locks as you go.
> Between those two bits of information, it should become apparent.

OK, so this doesn't look to be an index lock related thing at all here.  
Updated test case does this to create the table and show some additional 
state:

drop table t;
create table t (id bigint, value bigint);
insert into t(id,value) (select s,1 from generate_series(1,348) as s);
create index t_idx on t(id);
begin transaction;
set transaction isolation level serializable;
explain analyze select * from t where id = 2;
select pid,locktype,relation::regclass,page,tuple from pg_locks where 
mode='SIReadLock';
insert into t (id, value) values (-2, 1);
select pid,locktype,relation::regclass,page,tuple from pg_locks where 
mode='SIReadLock';

Do the same thing as before on the second process:

begin transaction;
set transaction isolation level serializable;
select * from t where id = 3;
insert into t (id, value) values (-3, 0);
commit;

Then return to the first client to commit.  When I execute that with 348 
records, the case that fails, it looks like this:

gsmith=# explain analyze select * from t where id = 2;                                        QUERY 
PLAN                                        
--------------------------------------------------------------------------------------------Seq Scan on t
(cost=0.00..6.35rows=2 width=16) (actual 
 
time=0.106..0.286 rows=1 loops=1)  Filter: (id = 2)Total runtime: 0.345 ms
(3 rows)

gsmith=# select pid,locktype,relation::regclass,page,tuple from pg_locks 
where mode='SIReadLock';pid  | locktype | relation | page | tuple
------+----------+----------+------+-------1495 | relation | t        |      |     

So it's actually grabbing a lock on the entire table in that situation.  
The other client does the same thing, and they collide with the 
described serialization failure.

The minute I try that with table that is 349 rows instead, it switches 
plans:

gsmith=# explain analyze select * from t where id = 2;                                                 QUERY 
PLAN                                                 
--------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on t  (cost=4.27..6.29 rows=2 width=16) (actual 
 
time=0.169..0.171 rows=1 loops=1)  Recheck Cond: (id = 2)  ->  Bitmap Index Scan on t_idx  (cost=0.00..4.27 rows=2
width=0)
 
(actual time=0.144..0.144 rows=1 loops=1)        Index Cond: (id = 2)Total runtime: 0.270 ms
(5 rows)

gsmith=# select pid,locktype,relation::regclass,page,tuple from pg_locks 
where mode='SIReadLock';pid  | locktype | relation | page | tuple
------+----------+----------+------+-------1874 | page     | t_idx    |    1 |     1874 | tuple    | t        |    0 |
  2
 
(2 rows)

Grabbing a lock on the index page and the row, as Dan explained it 
would.  This actually eliminates this particular serialization failure 
altogether here though, even with these still on the same table and 
index page.

So the root problem with Vlad's test isn't the index lock at all; it's 
heavy locking from the sequential scan that's executing on the trivial 
cases.  If he expands his tests to use a larger amount of data, such 
that the plan switches to a realistic one, his results with the new 
serialization mode may very well be more satisfying.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us