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
Attachment
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