public inbox for [email protected]  

help / color / mirror / Atom feed

From: Etsuro Fujita <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject:  Re: Options to control remote transactions’ access/deferrable modes in postgres_fdw
Date: Tue, 25 Mar 2025 11:44:43 +0100
Message-ID: <CAPmGK16OX-Z-A+pJuA8pZA693fEpXMgvuL6fQbAmexzJAx2wkg@mail.gmail.com> (raw)
In-Reply-To: <CAExHW5vOH-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com>
References: <CAPmGK16n_hcUUWuOdmeUS+w4Q6dZvTEDHb=OP=5JBzo-M3QmpQ@mail.gmail.com>
	<CAExHW5vOH-=1KhaL8S4xVVzSozvrbmbBVg97p0obwEW3sD57Cw@mail.gmail.com>

On Mon, Mar 3, 2025 at 1:51 PM Ashutosh Bapat
<[email protected]> wrote:
> On Sun, Mar 2, 2025 at 5:14 PM Etsuro Fujita <[email protected]> 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 = 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.gma...

view thread (20+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re:  Re: Options to control remote transactions’ access/deferrable modes in postgres_fdw
  In-Reply-To: <CAPmGK16OX-Z-A+pJuA8pZA693fEpXMgvuL6fQbAmexzJAx2wkg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox