Re: Index Skip Scan

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 <florisvannee(at)optiver(dot)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
>