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

Robert Haas <robertmhaas@gmail.com> writes:

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

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

On Fri, Dec 6, 2024 at 7:50 PM Andy Fan <zhihuifan1213@163.com> wrote:
> 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?

I think it might be a good idea to extend simple/extend query
protocols that way, but even if so, I would like to leave that for
future work, because even without that, I think this is still an
improvement, and I do not want to set the goal for the first cut too
high.

Having said that, if the next query uses simple query protocol, we can
avoid the extra communication by sending the two queries in a single
function call.  I will do that in the next version.

Thanks for the comment!

Best regards,
Etsuro Fujita