Re: BUG #17889: Invalid cursor direction for a foreign scan that reached the fetch_size (MOVE BACKWARD ALL IN cX)

I CCed Tom.

On Fri, Apr 7, 2023 at 6:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Fri, Apr 7, 2023 at 5:21 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> > The scenario is the following:
> > On DB 1 have
> > 3 tables, a view using a where clause on table 1 and a view using a inner
> > join on table 1 and 2.
> >
> > On DB 2 have
> > A fdw server(with use_remote_estimate set to true), 3 foreign tables, one
> > for each view and one for table 3 and a view on the foreign table of view of
> > table 1 (with a cte using a function and used in where clause)
> >
> > On DB 2 execute a SELECT on view of foreign table 1 with a join on foreign
> > table 2 with a where clause using a subquery on foreign table 3.
> >
> > If the SELECT would return an amount of rows equal or greater than the fetch
> > size of foreign table of view of table 1 the error will occur.
>
> > The same scenario was tested on Postgres 10 and 14, both worked without
> > error.
>
> > The error is the following:
> >
> > ERROR: cursor can only scan forward Hint:
> > Declare it with SCROLL option to enable backward scan.
> > Where: remote SQL command: MOVE BACKWARD ALL IN c3
>
> Will look into this.

Here is a simple reproducer:

create server loopback foreign data wrapper postgres_fdw options
(dbname 'postgres');
create user mapping for current_user server loopback;
create table loc1 (a int, b text);
create table loc2 (a int, b text);
insert into loc1 select i, 'loc1' from generate_series(1, 1000) i;
insert into loc2 select i, 'loc2' from generate_series(1, 2) i;
create foreign table rem1 (a int, b text) server loopback options
(table_name 'loc1');
analyze rem1;
analyze loc2;
set enable_mergejoin to false;
set enable_hashjoin to false;
set enable_material to false;

explain verbose update rem1 set b = rem1.b || rem1.b from loc2 where
rem1.a = loc2.a and random() > 0.0;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Update on public.rem1  (cost=100.00..308.02 rows=0 width=0)
   Remote SQL: UPDATE public.loc1 SET b = $2 WHERE ctid = $1
   ->  Nested Loop  (cost=100.00..308.02 rows=1 width=77)
         Output: (rem1.b || rem1.b), rem1.ctid, rem1.*, loc2.ctid
         Join Filter: ((rem1.a = loc2.a) AND (random() > '0'::double precision))
         ->  Seq Scan on public.loc2  (cost=0.00..1.02 rows=2 width=10)
               Output: loc2.ctid, loc2.a
         ->  Foreign Scan on public.rem1  (cost=100.00..136.00
rows=1000 width=48)
               Output: rem1.b, rem1.ctid, rem1.*, rem1.a
               Remote SQL: SELECT a, b, ctid FROM public.loc1 FOR UPDATE
(10 rows)

update rem1 set b = rem1.b || rem1.b from loc2 where rem1.a = loc2.a
and random() > 0.0;
ERROR:  cursor can only scan forward
HINT:  Declare it with SCROLL option to enable backward scan.
CONTEXT:  remote SQL command: MOVE BACKWARD ALL IN c1

I think that the root cause is in commit d844cd75a, which disallowed
rewinding and then re-fetching forwards in a NO SCROLL cursor.  I am
not sure what to do about this issue, but I am wondering whether that
commit is too restrictive, because 1) these examples would work just
fine without that commit, and 2) we still allow
rewind-and-fetch-forwards in a SCROLL cursor even when the query
includes volatile functions.

Best regards,
Etsuro Fujita