Cross-version Compatibility of postgres_fdw

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

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.

On Tue, Aug 20, 2024 at 8:55 AM Bruce Momjian <bruce@momjian.us> wrote:

I think it is an improvement, so applied to master. Thanks.

Thanks for taking care of this!

Best regards,
Etsuro Fujita