PostgreSQL read commit on different transactions

Question

I was running some tests to better understanding read commits for postgresql. I have two transactions running in parallel:

-- transaction 1
begin;
select id from item order by id asc FETCH FIRST 500 ROWS ONLY;
select pg_sleep(10);
commit;
--transaction 2 
begin;
select id from item order by id asc FETCH FIRST 500 ROWS ONLY;
commit;

The first transaction will select first 500 ids and then hold the id by sleeping 10s The second transaction will in the mean while querying for first 500 rows in the table.

Based my understanding of read commits, first transaction will select 1 to 500 records and second transaction will select 501 to 1000 records. But the actual result is that both two transactions select 1 to 500 records.

I will be really appreciated if someone can point out which part is wrong. Thanks

Answer 1

You are misinterpreting the meaning of read committed. It means that a transaction cannot see (select) updates that are not committed. Try the following:

create table read_create_test( id integer generated always as identity 
                             , cola text 
                             ) ; 
                             
insert into read_create_test(cola)
  select 'item-' || to_char(n,'fm000')
    from generate_series(1,50) gs(n); 
    
-- transaction 1 
do $$
   max_val integer; 
begin
   insert into read_create_test(cola)
     select 'item2-' || to_char(n+100,'fm000')
       from generate_series(1,50) gs(n);
      
   select max(id)
     into max_val
     from read_create_test; 
   raise notice 'Transaction 1 Max id: %',max_val;
   select pg_sleep(30);      -- make sure 2nd transaction has time to start 
   commit; 
end;
$$; 

-- transaction 2 (run after transaction 1 begins but before it ends)  
do $$
   max_val integer; 
begin
   select max(id)
     into max_val
     from read_create_test; 
    
   raise notice 'Transaction 2 Max id: %',max_val;
end;
$$; 

-- transaction 3 (run after transaction 1 ends)  
do $$
   max_val integer; 
begin
   select max(id)
     into max_val
     from read_create_test; 
    
   raise notice 'Transaction 3 Max id: %',max_val;
end;
$$;

Analyze the results keeping in mind that A transaction cannot see uncommitted DML.