Oh, interesting, thank you. I believe I know what happened, there is one unnecessary locking part that eventually gives only problems, plus one direct access to a page items without _bt_readpage. Will post a new version soon.
On Mon, Jan 27, 2020 at 3:00 PM Floris Van Nee <florisvan...@optiver.com> wrote: > > Hi Dmitry, > > Thanks for the new patch! I tested it and managed to find a case that causes > some issues. Here's how to reproduce: > > drop table if exists t; > create table t as select a,b,b%2 as c,10 as d from generate_series(1,5) a, > generate_series(1,1000) b; > create index on t (a,b,c,d); > > -- correct > postgres=# begin; declare c scroll cursor for select distinct on (a) a,b,c,d > from t order by a desc, b desc; fetch forward all from c; fetch backward all > from c; commit; > BEGIN > DECLARE CURSOR > a | b | c | d > ---+------+---+---- > 5 | 1000 | 0 | 10 > 4 | 1000 | 0 | 10 > 3 | 1000 | 0 | 10 > 2 | 1000 | 0 | 10 > 1 | 1000 | 0 | 10 > (5 rows) > > a | b | c | d > ---+------+---+---- > 1 | 1000 | 0 | 10 > 2 | 1000 | 0 | 10 > 3 | 1000 | 0 | 10 > 4 | 1000 | 0 | 10 > 5 | 1000 | 0 | 10 > (5 rows) > > -- now delete some rows > postgres=# delete from t where a=3; > DELETE 1000 > > -- and rerun: error is thrown > postgres=# begin; declare c scroll cursor for select distinct on (a) a,b,c,d > from t order by a desc, b desc; fetch forward all from c; fetch backward all > from c; commit; > BEGIN > DECLARE CURSOR > a | b | c | d > ---+------+---+---- > 5 | 1000 | 0 | 10 > 4 | 1000 | 0 | 10 > 2 | 1000 | 0 | 10 > 1 | 1000 | 0 | 10 > (4 rows) > > ERROR: lock buffer_content is not held > ROLLBACK > > > A slightly different situation arises when executing the cursor with an ORDER > BY a, b instead of the ORDER BY a DESC, b DESC: > -- recreate table again and execute the delete as above > > postgres=# begin; declare c scroll cursor for select distinct on (a) a,b,c,d > from t order by a, b; fetch forward all from c; fetch backward all from c; > commit; > BEGIN > DECLARE CURSOR > a | b | c | d > ---+---+---+---- > 1 | 1 | 1 | 10 > 2 | 1 | 1 | 10 > 4 | 1 | 1 | 10 > 5 | 1 | 1 | 10 > (4 rows) > > a | b | c | d > ---+-----+---+---- > 5 | 1 | 1 | 10 > 4 | 1 | 1 | 10 > 2 | 827 | 1 | 10 > 1 | 1 | 1 | 10 > (4 rows) > > COMMIT > > And lastly, you'll also get incorrect results if you do the delete slightly > differently: > -- leave one row where a=3 and b=1000 > postgres=# delete from t where a=3 and b<=999; > -- the cursor query above won't show any of the a=3 rows even though they > should > > > -Floris >
