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

I resent this, because I forgot to CC it to Tom.  :(

On Sat, Apr 29, 2023 at 8:36 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> 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