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

On Tue, Jul 16, 2024 at 5:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> > This causes eg, a join-UPDATE query where multiple rows join to the
> > same foreign target row to repeatedly update the target row, as shown
> > below, which would never happen if rewinding the cursor.
> > ...
> > Note that postgres_fdw already recreates a cursor when doing a rescan
> > with parameter changes, so we already have this issue.  IMO I think we
> > should avoid writing a query like this.
>
> Hmm.  In principle, since postgres_fdw controls all the SQL sent to
> the remote side, we could avoid building problematic queries.  But
> I'm not sure how to make that work in practice, or how we'd avoid
> somebody carelessly breaking it in future.  It seems like the
> property you propose requiring is a second-order effect that would
> be hard to ensure.

Agreed.  To be honest I am not sure if we can fix this issue, but if
so, I think that that would be going to require invasive changes to
the core and probably would not be back-patchable, so I will leave
this for future work.

Best regards,
Etsuro Fujita