Cross-version Compatibility of postgres_fdw
Hi,
The documentation states that postgres_fdw can be used with remote servers
as far back as PostgreSQL 8.3.
https://www.postgresql.org/docs/devel/postgres-fdw.html#POSTGRES-FDW-CROSS-VERSION-COMPATIBILITY
However, when using PostgreSQL 9.4 or earlier as a remote server,
INSERT ON CONFLICT on a foreign table fails because this feature
is only supported in v9.5 and later. Should we add a note to
the documentation to clarify this limitation?
For example:
"Another limitation is that when executing INSERT statements with
an ON CONFLICT DO NOTHING clause on a foreign table, the remote server
must be running PostgreSQL 9.5 or later, as earlier versions do not
support this feature."
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Wed, Aug 7, 2024 at 9:32 PM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
However, when using PostgreSQL 9.4 or earlier as a remote server,
INSERT ON CONFLICT on a foreign table fails because this feature
is only supported in v9.5 and later. Should we add a note to
the documentation to clarify this limitation?
+1
For example:
"Another limitation is that when executing INSERT statements with
an ON CONFLICT DO NOTHING clause on a foreign table, the remote server
must be running PostgreSQL 9.5 or later, as earlier versions do not
support this feature."
We already have this note in the documentation:
“Note that postgres_fdw currently lacks support for INSERT statements
with an ON CONFLICT DO UPDATE clause. However, the ON CONFLICT DO
NOTHING clause is supported, provided a unique index inference
specification is omitted.”
So yet another idea is to expand the latter part a little bit like:
However, the ON CONFLICT DO NOTHING clause is supported, provided a
unique index inference specification is omitted and the remote server
is 9.5 or later.
I just thought consolidating the information to one place would make
the documentation more readable.
Best regards,
Etsuro Fujita
On 2024/08/09 17:49, Etsuro Fujita wrote:
I just thought consolidating the information to one place would make
the documentation more readable.
Yes, so I think that adding a note about the required remote server version
to the cross-version compatibility section would be more intuitive.
This section already discusses the necessary server versions and limitations.
Patch attached.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v1-0001-doc-Add-note-on-postgres_fdw-cross-version-compat.patchtext/plain; charset=UTF-8; name=v1-0001-doc-Add-note-on-postgres_fdw-cross-version-compat.patchDownload
From dd1a9544d30f9cb1c3a3a0c3bb1d8dcfb615af08 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Tue, 13 Aug 2024 11:10:18 +0900
Subject: [PATCH v1] doc: Add note on postgres_fdw cross-version compatibility
limitations.
The documentation mentions that postgres_fdw can be used with remote
servers dating back to PostgreSQL 8.3. However, when using PostgreSQL 9.4
or earlier as a remote server, INSERT ON CONFLICT on a foreign table fails
because this feature is only supported in version 9.5 and later.
This commit adds a note clarifying that when executing INSERT statements
with an ON CONFLICT DO NOTHING clause on a foreign table, the remote
server must be running PostgreSQL 9.5 or later, as earlier versions do not
support this feature.
Back-patch to all supported versions.
Discussion: https://postgr.es/m/47801526-d017-4c89-9f52-c02c449a139b@oss.nttdata.com
---
doc/src/sgml/postgres-fdw.sgml | 12 +++++++++++-
1 file changed, 11 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 90969f63ca..468724e94e 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -1096,7 +1096,10 @@ postgres=# SELECT postgres_fdw_disconnect_all();
<para>
<filename>postgres_fdw</filename> can be used with remote servers dating back
to <productname>PostgreSQL</productname> 8.3. Read-only capability is available
- back to 8.1. A limitation however is that <filename>postgres_fdw</filename>
+ back to 8.1.
+ </para>
+ <para>
+ A limitation however is that <filename>postgres_fdw</filename>
generally assumes that immutable built-in functions and operators are
safe to send to the remote server for execution, if they appear in a
<literal>WHERE</literal> clause for a foreign table. Thus, a built-in
@@ -1108,6 +1111,13 @@ postgres=# SELECT postgres_fdw_disconnect_all();
optimization fence, and placing the problematic function or operator
outside the sub-<literal>SELECT</literal>.
</para>
+ <para>
+ Another limitation is that when executing <command>INSERT</command>
+ statements with an <literal>ON CONFLICT DO NOTHING</literal> clause on
+ a foreign table, the remote server must be running
+ <productname>PostgreSQL</productname> 9.5 or later,
+ as earlier versions do not support this feature.
+ </para>
</sect2>
<sect2 id="postgres-fdw-wait-events">
--
2.45.2
Fujii Masao <masao.fujii@oss.nttdata.com> writes:
Yes, so I think that adding a note about the required remote server version
to the cross-version compatibility section would be more intuitive.
This section already discusses the necessary server versions and limitations.
Patch attached.
This discussion tickles a concern I've had for awhile: do we really
know that modern postgres_fdw would work with an 8.3 server (never
mind 8.1)? How many of us are in a position to test or debug such
a setup? The discussions we've had around old-version compatibility
for pg_dump and psql seem just as relevant here.
In short, I'm wondering if we should move up the goalposts and only
claim compatibility back to 9.2.
It'd be even better if we had some routine testing to verify that
such cases work. I'm not volunteering to make that happen, though.
regards, tom lane
On Tue, Aug 13, 2024 at 12:15 PM Fujii Masao
<masao.fujii@oss.nttdata.com> wrote:
On 2024/08/09 17:49, Etsuro Fujita wrote:
I just thought consolidating the information to one place would make
the documentation more readable.Yes, so I think that adding a note about the required remote server version
to the cross-version compatibility section would be more intuitive.
This section already discusses the necessary server versions and limitations.
Patch attached.
Thanks for the patch!
I noticed that we already have mentioned such a version limitation on
the analyze_sampling option outside that section (see the description
for that option in the “Cost Estimation Options” section). So my
concern is that it might be inconsistent to state only the INSERT
limitation there. Maybe I am too worried about that.
Best regards,
Etsuro Fujita
On Tue, Aug 13, 2024 at 12:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
This discussion tickles a concern I've had for awhile: do we really
know that modern postgres_fdw would work with an 8.3 server (never
mind 8.1)? How many of us are in a position to test or debug such
a setup? The discussions we've had around old-version compatibility
for pg_dump and psql seem just as relevant here.In short, I'm wondering if we should move up the goalposts and only
claim compatibility back to 9.2.It'd be even better if we had some routine testing to verify that
such cases work. I'm not volunteering to make that happen, though.
+1 to both. Unfortunately, I do not think I will have time for that, though.
Best regards,
Etsuro Fujita
On Wed, Aug 14, 2024 at 03:25:48AM +0900, Etsuro Fujita wrote:
On Tue, Aug 13, 2024 at 12:15 PM Fujii Masao
<masao.fujii@oss.nttdata.com> wrote:On 2024/08/09 17:49, Etsuro Fujita wrote:
I just thought consolidating the information to one place would make
the documentation more readable.Yes, so I think that adding a note about the required remote server version
to the cross-version compatibility section would be more intuitive.
This section already discusses the necessary server versions and limitations.
Patch attached.Thanks for the patch!
I noticed that we already have mentioned such a version limitation on
the analyze_sampling option outside that section (see the description
for that option in the “Cost Estimation Options” section). So my
concern is that it might be inconsistent to state only the INSERT
limitation there. Maybe I am too worried about that.
I think it is an improvement, so applied to master. Thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.