Re: Partial aggregates pushdown

Bruce Momjian писал 2023-06-05 19:26:
> On Mon, Jun 5, 2023 at 12:00:27PM +0300, Alexander Pyhalov wrote:
>> Note that after these changes "select sum()" will fail for certain
>> cases,
>> when remote server version is not the latest. In other cases we tried
>> to preserve compatibility. Should we have a switch for a foreign
>> server to
>> turn this optimization off? Or do we just state that users
>> should use different workarounds if remote server version doesn't
>> match
>> local one?
>
> We covered this in April in this and previous emails:
>
> https://www.postgresql.org/message-id/ZDGTza4rovCa%2BN3d%40momjian.us
>
> We don't check the version number for _any_ builtin functions so why
> would we need to check for aggregate pushdown? Yes, these will be new
> functions in PG 17, we have added functions regularly in major releases
> and have never heard reports of problems about that.
>
Hi.

I've seen this message. But introduction of new built-in function will
break requests to old servers
only if this new function is used in the request (this means that query
changes). However, this patch
changes the behavior of old queries, which worked prior to update. This
seems to be different to me.
Also I see that in connection.c (configure_remote_session()), we care
about old PostgreSQL versions.
And now we make querying them more tricky. Is it consistent? Do you
think that
enable_partitionwise_aggregate is a good enough protection in this
cases?

In documentation I see

"F.38.7. Cross-Version Compatibility
postgres_fdw can be used with remote servers dating back to PostgreSQL
8.3. Read-only capability is available back to 8.1. A limitation however
is that postgres_fdw generally assumes that immutable built-in functions
and operators are safe to send to the remote server for execution, if
they appear in a WHERE clause for a foreign table. Thus, a built-in
function that was added since the remote server's release might be sent
to it for execution, resulting in “function does not exist” or a similar
error. This type of failure can be worked around by rewriting the query,
for example by embedding the foreign table reference in a sub-SELECT
with OFFSET 0 as an optimization fence, and placing the problematic
function or operator outside the sub-SELECT."

Likely, this paragraph should be expanded to state that partition-wise
aggregation for many functions can fail to work with old foreign
servers.

--
Best regards,
Alexander Pyhalov,
Postgres Professional