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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)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