Thread: 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

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