Re: Aggregate Push Down - Performing aggregation on foreign server

On Fri, Oct 21, 2016 at 9:09 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Oct 21, 2016 at 11:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Fri, Oct 21, 2016 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> dromedary seems to have found one, or at least an unstable test result.
>>
>>> OK, looking at that now. Thanks.
>>
>> Looking at further failures, it looks like 32-bit machines in general
>> get that result. Might be just a cost estimation difference.
>>
>> Also, some of the windows machines are folding "sqrt(2)" to a different
>> constant than is hard-wired into the expected-result file. That's
>> slightly annoying because it calls into question whether we can ship
>> floating-point computations to the far end at all :-(.
>
> IMHO, not shipping floating-point computations for that reason would
> be more annoying than helpful.

+1

> To really guarantee that the remote
> and identical results are identical, you'd need far more
> infrastructure than we have - you'd have to make sure the operating
> system collations matched, for example. And we're already assuming
> (without any proof) that the default collations match and, for that
> matter, that the datatypes match. If you're pushing down to
> PostgreSQL on the other end, you at least have a hope that the other
> side might be sufficiently identical to the local side that the
> results will be the same, but if somebody implements these pushdowns
> for Oracle or SQL server, you almost might as well give up and go
> home. Even for PostgreSQL, getting the same results in every possible
> corner case requires a certain degree of optimism.
>
> For my money, the way to handle that is to add more control over
> pushdown rather than automatically disabling it in certain cases. For
> example, we could have GUCs that disable all pushdown or certain types
> of pushdown - e.g. you could specify that you don't trust the remote
> side to sort data properly, or that you don't like it's floating-point
> implementation. I'm not sure what controls are most useful here, but
> I'd be willing to bet you a nice glass of white wine that many people
> will be happier with a system that they can control than they will be
> with one that just disables the optimization in every case where it
> might hypothetically not work out. It's not clear that we can even
> reasonably foresee all such cases.

+1. But having too many of them will make the customer go crazy.
Everytime s/he stumbles upon such a problem (or bug from her/his point
of view), we will add a GUC/option OR tell, "oh! you have to turn
on/off that GUC/option". That will just make the users loose faith in
FDW (or at least postgres_fdw), if we end up with too many of them.

Instead, we may want to add the intelligence to detect when floating
point calculations on the foreign server are different from local one
OR that the default locale or collation on the foreign server are
different from the local one and accordingly set the foreign server
options automatically. This may be part of the postgres_fdw or a
separate utility. I guess, this will be similar to what configure does
to detect compatible libraries. The information gathered will
certainly become obsolete if the user re-configures the server or
changes settings, but at least we can advice them to run the utility
to detect any inconsistencies caused by changes and report or correct
them. I think this is a bigger project, separate from any FDW
pushdown.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company