MIME-Version: 1.0
References: 
 <CAPmGK16n_hcUUWuOdmeUS+w4Q6dZvTEDHb=OP=5JBzo-M3QmpQ@mail.gmail.com>
 <CAExHW5vOH-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com>
In-Reply-To: 
 <CAExHW5vOH-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com>
From: Etsuro Fujita <etsuro.fujita@gmail.com>
Date: Tue, 25 Mar 2025 11:44:43 +0100
Message-ID: 
 <CAPmGK16OX-Z-A+pJuA8pZA693fEpXMgvuL6fQbAmexzJAx2wkg@mail.gmail.com>
Subject: 
 =?UTF-8?Q?Re=3A_Options_to_control_remote_transactions=E2=80=99_access?=
	=?UTF-8?Q?=2Fdeferrable_modes_in_postgres=5Ffdw?=
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAPmGK16OX-Z-A%2BpJuA8pZA693fEpXMgvuL6fQbAmexzJAx2wkg%40mail.gmail.com>
Precedence: bulk

On Mon, Mar 3, 2025 at 1:51=E2=80=AFPM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> On Sun, Mar 2, 2025 at 5:14=E2=80=AFPM Etsuro Fujita <etsuro.fujita@gmail=
.com> wrote:
> > postgres_fdw opens remote transactions in read/write mode in a local
> > transaction even if the local transaction is read-only.  I noticed
> > that this leads to surprising behavior like this:

> I am having a hard time deciding whether this is problematic behaviour
> or not. Maybe the way example is setup - it's querying a view on a
> remote database which doesn't return anything but modified data. If
> there is no modification happening on the foreign server it won't
> return any data. Thus we have no way to verify that the table changed
> because of a READ ONLY transaction which is not expected to change any
> data. Probably some other example which returns all the rows from test
> while modifying some of it might be better.

How about something like this?

CREATE TABLE loct (f1 int, f2 text);
CREATE FUNCTION locf() RETURNS SETOF loct LANGUAGE SQL AS
  'UPDATE public.loct SET f2 =3D f2 || f2 RETURNING *';
CREATE VIEW locv AS SELECT t.* FROM locf() t;
CREATE FOREIGN TABLE remt (f1 int, f2 text)
  SERVER loopback OPTIONS (table_name 'locv');
INSERT INTO loct VALUES (1, 'foo'), (2, 'bar');
SELECT * FROM loct;
 f1 | f2
----+-----
  1 | foo
  2 | bar
(2 rows)

SELECT * FROM remt;  -- should work
 f1 |   f2
----+--------
  1 | foofoo
  2 | barbar
(2 rows)

SELECT * FROM loct;
 f1 |   f2
----+--------
  1 | foofoo
  2 | barbar
(2 rows)

I added this test case to the updated patch [1].

Thanks for the comments!

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK14Btk0odkH6vwBhBGjCexmmWcM_=
D3DG0pJtObj8k_Unag%40mail.gmail.com