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(dot)fujita(at)gmail(dot)com> wrote:
> On Fri, Apr 7, 2023 at 5:21 AM PG Bug reporting form
> <noreply(at)postgresql(dot)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