pgsql: postgres_fdw: Inherit the local transaction's access/deferrable
postgres_fdw: Inherit the local transaction's access/deferrable modes.
Previously, postgres_fdw always 1) opened a remote transaction in READ
WRITE mode even when the local transaction was READ ONLY, causing a READ
ONLY transaction using it that references a foreign table mapped to a
remote view executing a volatile function to write in the remote side,
and 2) opened the remote transaction in NOT DEFERRABLE mode even when
the local transaction was DEFERRABLE, causing a SERIALIZABLE READ ONLY
DEFERRABLE transaction using it to abort due to a serialization failure
in the remote side.
To avoid these, modify postgres_fdw to open a remote transaction in the
same access/deferrable modes as the local transaction. This commit also
modifies it to open a remote subtransaction in the same access mode as
the local subtransaction.
Although these issues exist since the introduction of postgres_fdw,
there have been no reports from the field. So it seems fine to just fix
them in master only.
Author: Etsuro Fujita <etsuro.fujita@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: /messages/by-id/CAPmGK16n_hcUUWuOdmeUS+w4Q6dZvTEDHb=OP=5JBzo-M3QmpQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/e5a3c9d9b5ce535151d3a7e3173e8d27d2d8cd58
Modified Files
--------------
contrib/postgres_fdw/connection.c | 99 ++++++++++++++++--
contrib/postgres_fdw/expected/postgres_fdw.out | 134 +++++++++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 78 ++++++++++++++
doc/src/sgml/postgres-fdw.sgml | 15 +++
src/backend/access/transam/xact.c | 28 ++++++
src/include/access/xact.h | 1 +
6 files changed, 347 insertions(+), 8 deletions(-)
On 2025/06/01 17:34, Etsuro Fujita wrote:
postgres_fdw: Inherit the local transaction's access/deferrable modes.
Previously, postgres_fdw always 1) opened a remote transaction in READ
WRITE mode even when the local transaction was READ ONLY, causing a READ
ONLY transaction using it that references a foreign table mapped to a
remote view executing a volatile function to write in the remote side,
and 2) opened the remote transaction in NOT DEFERRABLE mode even when
the local transaction was DEFERRABLE, causing a SERIALIZABLE READ ONLY
DEFERRABLE transaction using it to abort due to a serialization failure
in the remote side.To avoid these, modify postgres_fdw to open a remote transaction in the
same access/deferrable modes as the local transaction. This commit also
modifies it to open a remote subtransaction in the same access mode as
the local subtransaction.Although these issues exist since the introduction of postgres_fdw,
there have been no reports from the field. So it seems fine to just fix
them in master only.
I'm not sure this change should be considered a bug fix,
since the current behavior of postgres_fdw with a local read-only
transaction isn't clearly documented. Some users might see this
as a behavioral change rather than a fix. Anyway if we go with it,
shouldn't we document the change in the v18 release notes?
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
On Mon, Jun 02, 2025 at 12:03:50PM +0900, Fujii Masao wrote:
I'm not sure this change should be considered a bug fix,
since the current behavior of postgres_fdw with a local read-only
transaction isn't clearly documented. Some users might see this
as a behavioral change rather than a fix. Anyway if we go with it,
shouldn't we document the change in the v18 release notes?
After going through the thread and the commit, I have to admit that I
was surprised to see this applied on HEAD now that we are in feature
freeze. This is a behavior change. Perhaps this could be done once
v19 happens, still it's rather unclear if the new behavior is better
than the previous one.
--
Michael
On Mon, Jun 2, 2025 at 12:33 PM Michael Paquier <michael@paquier.xyz> wrote:
On Mon, Jun 02, 2025 at 12:03:50PM +0900, Fujii Masao wrote:
I'm not sure this change should be considered a bug fix,
since the current behavior of postgres_fdw with a local read-only
transaction isn't clearly documented. Some users might see this
as a behavioral change rather than a fix. Anyway if we go with it,
shouldn't we document the change in the v18 release notes?After going through the thread and the commit, I have to admit that I
was surprised to see this applied on HEAD now that we are in feature
freeze. This is a behavior change. Perhaps this could be done once
v19 happens, still it's rather unclear if the new behavior is better
than the previous one.
No, this is a fix, not a feature, as discussed in the thread; as
mentioned in the commit message, the previous version of postgres_fdw
could cause surprising behaviors that would never happen in normal
cases where a read-only and/or deferrable transaction only
accesses/modifies data on the local server, so this commit fixes those
behaviors. But yes, it makes a behavior change, so I think it’s a
good idea to add a note about that to the v18 release notes, as
proposed by Fujii-san.
Thank you for the comments!
Best regards,
Etsuro Fujita
On 2025/06/03 19:45, Etsuro Fujita wrote:
On Mon, Jun 2, 2025 at 12:33 PM Michael Paquier <michael@paquier.xyz> wrote:
On Mon, Jun 02, 2025 at 12:03:50PM +0900, Fujii Masao wrote:
I'm not sure this change should be considered a bug fix,
since the current behavior of postgres_fdw with a local read-only
transaction isn't clearly documented. Some users might see this
as a behavioral change rather than a fix. Anyway if we go with it,
shouldn't we document the change in the v18 release notes?After going through the thread and the commit, I have to admit that I
was surprised to see this applied on HEAD now that we are in feature
freeze. This is a behavior change. Perhaps this could be done once
v19 happens, still it's rather unclear if the new behavior is better
than the previous one.No, this is a fix, not a feature, as discussed in the thread; as
mentioned in the commit message, the previous version of postgres_fdw
could cause surprising behaviors that would never happen in normal
cases where a read-only and/or deferrable transaction only
accesses/modifies data on the local server, so this commit fixes those
behaviors.
I agree this could be considered a fix if the new behavior has been
clearly explained in the documentation from before or based on
standards like SQL/MED. But if that's not the case, it seems more
like a behavior change. In that case, I think it should wait for v19
and be applied only after reaching consensus. Some systems might
rely on the previous behavior.
By the way, if a read-only transaction on the local server is meant
to block all write operations on the remote server, this patch alone
might not be sufficient, for example, that read-only transaction can
invoke a login trigger on the remote server and it could still
perform writes.
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
On Tue, Jun 3, 2025 at 6:45 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
No, this is a fix, not a feature, as discussed in the thread; as
mentioned in the commit message, the previous version of postgres_fdw
could cause surprising behaviors that would never happen in normal
cases where a read-only and/or deferrable transaction only
accesses/modifies data on the local server, so this commit fixes those
behaviors. But yes, it makes a behavior change, so I think it’s a
good idea to add a note about that to the v18 release notes, as
proposed by Fujii-san.
Sometimes, people can have different opinions about whether something
is a bug fix or a behavior change. So far, I don't think you've
convinced a single person either on the original thread or on this one
that this is a bug fix, so I believe that, at present, the consensus
is that this is a new feature. Although you may not agree with that
consensus, and you may even be right, we all have to do what most
people agree is right rather than what we ourselves prefer.
For what it's worth, I agree with others that this is not just a bug
fix: it's a behavior change that should be subject to the feature
freeze. I personally think that it's probably a desirable behavior
change, and that it's small enough that we could consider leaving it
in v18 if that meets with general approval. We have had cases like
this, where something feels too disruptive to back-patch, but is still
on some level a fix or correction of behavior, in the past, and we
have sometimes decided to handle those by allowing them to be added to
the major release after the feature freeze deadline, but not
back-patching them. So in my mind that is a possibility here.
However, that would require a pretty unanimous agreement that this
change is an improvement, and it appears to me that we don't have
that. I read Fujii Masao's comments to indicate that he doesn't
necessarily agree with the change and wants it reverted, and I read
Michael Paquier's comments the same way. Unless I'm misunderstanding
their position, this needs to be reverted.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Thu, Jun 5, 2025 at 3:39 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Jun 3, 2025 at 6:45 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
No, this is a fix, not a feature, as discussed in the thread; as
mentioned in the commit message, the previous version of postgres_fdw
could cause surprising behaviors that would never happen in normal
cases where a read-only and/or deferrable transaction only
accesses/modifies data on the local server, so this commit fixes those
behaviors. But yes, it makes a behavior change, so I think it’s a
good idea to add a note about that to the v18 release notes, as
proposed by Fujii-san.Sometimes, people can have different opinions about whether something
is a bug fix or a behavior change. So far, I don't think you've
convinced a single person either on the original thread or on this one
that this is a bug fix, so I believe that, at present, the consensus
is that this is a new feature. Although you may not agree with that
consensus, and you may even be right, we all have to do what most
people agree is right rather than what we ourselves prefer.
A consensus we reached on the original thread is that if the previous
behavior is considered problematic, we should fix it; otherwise, we
should not. I proposed to fix it for the reason mentioned above, and
went ahead, as there were no objections about that. But seeing the
comments on this thread, I have to agree that this is a feature rather
than a fix.
For what it's worth, I agree with others that this is not just a bug
fix: it's a behavior change that should be subject to the feature
freeze. I personally think that it's probably a desirable behavior
change, and that it's small enough that we could consider leaving it
in v18 if that meets with general approval. We have had cases like
this, where something feels too disruptive to back-patch, but is still
on some level a fix or correction of behavior, in the past, and we
have sometimes decided to handle those by allowing them to be added to
the major release after the feature freeze deadline, but not
back-patching them. So in my mind that is a possibility here.However, that would require a pretty unanimous agreement that this
change is an improvement, and it appears to me that we don't have
that. I read Fujii Masao's comments to indicate that he doesn't
necessarily agree with the change and wants it reverted, and I read
Michael Paquier's comments the same way. Unless I'm misunderstanding
their position, this needs to be reverted.
Agreed. I will revert this in a few days. And I will re-propose it
as an improvement for v19.
Thanks for the discussion!
Best regards,
Etsuro Fujita