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

From: Robert Haas <[email-masked]>
Date: Thu, 5 Dec 2024 12:37:02 -0500

On Thu, Dec 5, 2024 at 4:41 AM Etsuro Fujita <[email-masked]> 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

---

From: Andy Fan <[email-masked]>
Date: Fri, 06 Dec 2024 18:50:25 +0800

> ...fetch more rows from cursor foo until we start scanning ft2...

Apart from the above issue, what do you think about that we are using a
'SELECT pg_catalog.pg_refresh_snapshot()' to let the remote do the
refresh_snapshot VS 'a new message type for this'?  There are lots of
things happen in the 'SELECT' way like 'a extra network communication',
'a complete parser-planner-executor workflow.' With a new message type
for this, we can send the message character with the next query
together. if so, can the two overheads removed? 

-- 
Best Regards
Andy Fan

---

From: Etsuro Fujita <[email-masked]>
Date: Sat, 7 Dec 2024 18:02:55 +0900

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