On Tue, Oct 24, 2023 at 8:48 PM Devrim Gündüz <dev...@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]. Best regards, Etsuro Fujita [1] https://www.postgresql.org/message-id/CAPmGK149UubRQGLH6QaBkhJvas%2BGz%2BT6tx2MBX9MTJpxDRKPBA%40mail.gmail.com
