Thread: Re: postgres_fdw: Provide better emulation of READ COMMITTED behavior

On Thu, Dec 5, 2024 at 4:41 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> Comments welcome!  Maybe I am missing something, though.

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...

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

On Fri, Dec 6, 2024 at 2:37 AM Robert Haas <robertmhaas@gmail.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.

Thank you for taking the time for this proposal!

Best regards,
Etsuro Fujita