Re: postgres_fdw: transaction mode inheritance corner cases

On Thu, Oct 1, 2026 at 3:27 AM Matheus Alcantara
<matheusssilv97(at)gmail(dot)com> wrote:
> 1: read-write local transactions can no longer query a hot standby

Ah, I think this is too restrictive, so I changed my mind; I'd like to
propose to keep the current behavior and instead modify the
documentation like this:

> 2: a foreign cursor first fetched in a rolled-back savepoint breaks at COMMIT
>
> Repro:
>
> begin; declare c cursor for select * from ft;
> savepoint s1; fetch 1 from c; rollback to s1;
> fetch all from c; commit;
> ERROR: 34000: cursor "c1" does not exist
> CONTEXT: remote SQL command: CLOSE c1
>
> This also works on unpatched master. There, the remote cursor is created
> lazily at the first FETCH, without a remote savepoint, so it lives at
> remote level 1 and survives ROLLBACK TO s1. With the patch, the new
> begin_remote_xact() call in create_cursor opens a remote SAVEPOINT s2
> and creates the cursor inside it. ROLLBACK TO s1 then destroys the
> remote cursor while the local side still thinks it exists.

Reproduced here. I think your analysis is correct, but I noticed that
this is an existing issue in postgres_fdw even in v18 and older. Here
is an example:

begin;
declare c cursor for select * from ft1;
savepoint s;
select * from ft1;
a | b
---+---
1 | 1
2 | 2
(2 rows)

fetch 2 from c;
a | b
---+---
1 | 1
2 | 2
(2 rows)

rollback to s;
release savepoint s;
commit;
ERROR: cursor "c1" does not exist
CONTEXT: remote SQL command: CLOSE c1

> Also, I didn't tested this case but on execute_foreign_modify and
> direct-modify results are read without going through
> begin_remote_xact(). So I'm wondering if a volatile function that runs
> SET TRANSACTION READ ONLY in the middle of a single INSERT ... SELECT
> f() could bypass the sync.

DML statements are prevented on the local server if in READ ONLY mode,
so those functions are never run in that mode. No?

Thanks for the review!

Best regards,
Etsuro Fujita