d844cd75a and postgres_fdw

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:
/messages/by-id/1852635.1682808624@sss.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]/messages/by-id/CAPmGK149UubRQGLH6QaBkhJvas+Gz+T6tx2MBX9MTJpxDRKPBA@mail.gmail.com.

Best regards,
Etsuro Fujita

[1]: /messages/by-id/CAPmGK149UubRQGLH6QaBkhJvas+Gz+T6tx2MBX9MTJpxDRKPBA@mail.gmail.com

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:
/messages/by-id/1852635.1682808624@sss.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

Hi,

On Fri, 2024-07-19 at 14:01 +0900, Etsuro Fujita wrote:

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

Thanks a lot!

Regards,
--
Devrim Gündüz
Open Source Solution Architect, PostgreSQL Major Contributor
Twitter: @DevrimGunduz , @DevrimGunduzTR