Re: postgres_fdw: Provide better emulation of READ COMMITTED behavior

On Sat, Dec 7, 2024 at 4:03 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> On Fri, Dec 6, 2024 at 2:37 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > I have a hard time seeing how this would work if cursors are in use on
> > the main server. Say I do this:
> >
> > DECLARE foo CURSOR FOR SELECT * FROM ft1 UNION ALL SELECT * FROM ft2;
> > ...fetch some rows from cursor foo but few enough that we only scan ft1...
> > ...do something that causes a snapshot refresh like issue another query...
> > ...fetch more rows from cursor foo until we start scanning ft2...
>
> Good point! Maybe my explanation was not enough, but the proposed
> patch does not allow any query to do a snapshot refresh if such open
> cursors exist on the main server, so cursor foo is guaranteed to scan
> ft2 using the same snapshot that was used to scan ft1.

OK, I see. That does prevent the hazard I mentioned, but it also means
that the results returned by a query are dependent on whether there's
an unrelated cursor open, which seems unfortunate.

--
Robert Haas
EDB: http://www.enterprisedb.com