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
forwardall 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
forwardall 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
fromc; 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