Re: d844cd75a and postgres_fdw

On Fri, Jul 5, 2024 at 9:56 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Tue, Oct 24, 2023 at 8:48 PM Devrim Gündüz <devrim@gunduz.org> wrote:
> > I'm seeing an issue after upgrading from 12.13 to 15.4. This happens
> > when we run a query against a foreign table (fdw on the same instance to
> > a different database) -- but does not appear when we get rid of
> > postgres_fdw:
> >
> > 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
> >
> > SQL state: 55000
> >
> > I attached the query. The name of the foreign table is
> > "foobar.sys_user".
> >
> > Looks like the bug #17889, and this is the last email in that thread:
> > https://www.postgresql.org/message-id/1852635.1682808624%40sss.pgh.pa.us
> >
> > OTOH, same query works (against the FDW) when we remove the following
> > WHERE clause:
> >
> > WHERE
> >     tbl.table_status = 'A'
> >     AND tbl.table_id <> 1
> >     AND tbl.table_id <> - 2
> >
> > Any hints?
>
> The error occurs when rescanning a postgres_fdw foreign relation, so I
> think the reason why the query works would be that the planner chose a
> join plan other than a nestloop join plan.
>
> I proposed a fix for this in [1].

I pushed the fix and back-patched to v15.  Thanks for the report!

Best regards,
Etsuro Fujita