Re: [HACKERS] postgres_fdw aggregation pushdown has collation change in 10beta.

[Ashutosh Bapat]

On Thu, May 18, 2017 at 12:37 AM, Jeff Janes  wrote:
> It is shipping collation-sensitive aggregates between servers which have
> different collations.
>
> commit 7012b132d07c2b4ea15b0b3cb1ea9f3278801d98
> Author: Robert Haas 
> Date:   Fri Oct 21 09:54:29 2016 -0400
>
> postgres_fdw: Push down aggregates to remote servers.
>
>
> I've attached a reproducing case.  Before this commit, the two final queries
> give the same answer, and after they give different answers.  Maybe this
> isn't considered a bug?  Is it just one of the "surprising semantic
> anomalies may arise when types or collations do not match"?  It should be
> able to know what collation the local definition of the foreign table has;
> couldn't it pass that collation over the foreign side?
>
> I don't really care, I was just using min as a way to get an arbitrary value
> in the cases where there are more than one, but I found the change
> surprising.

Per [1]
--
COLLATE collation

The COLLATE clause assigns a collation to the column (which must be of
a collatable data type). If not specified, the column data type's
default collation is used.
--

In your test you have not specified the collation for column x in
remote1, so it's considered as DEFAULT collation on the local server.
>From the POV of the local server, the collation of the column on the
foreign server is same as the default collation locally, so it doesn't
add any collation clause to the query. May be it could, but then the
problem is that the exact default collation on local server may not be
available on the foreign server. What foreign server has might be a
collation whose behaviour is same as local server's default collation
behaviour, as far as that column's data type is concerned.

But this is not something specific to aggregation, WHERE, ORDER BY
clauses pushed down to the foreign server have the same behaviour.

[1] https://www.postgresql.org/docs/devel/static/sql-createforeigntable.html

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] postgres_fdw aggregation pushdown has collation change in 10beta.

[Jeff Janes]

It is shipping collation-sensitive aggregates between servers which have
different collations.

commit 7012b132d07c2b4ea15b0b3cb1ea9f3278801d98
Author: Robert Haas 
Date:   Fri Oct 21 09:54:29 2016 -0400

postgres_fdw: Push down aggregates to remote servers.


I've attached a reproducing case.  Before this commit, the two final
queries give the same answer, and after they give different answers.  Maybe
this isn't considered a bug?  Is it just one of the "surprising semantic
anomalies may arise when types or collations do not match"?  It should be
able to know what collation the local definition of the foreign table has;
couldn't it pass that collation over the foreign side?

I don't really care, I was just using min as a way to get an arbitrary
value in the cases where there are more than one, but I found the change
surprising.

Cheers,

Jeff
drop database foobar_C;
drop database foobar_US;
create database foobar_C encoding 'utf-8' lc_collate "C" template template0;
create database foobar_US encoding 'utf-8' lc_collate "en_US.utf8" template 
template0;

\c foobar_us

create table remote1 (x text);
\copy remote1 from stdin
a
P
\.

\c foobar_c

CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public;

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw options (dbname 
'foobar_us') ;
CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (
"user" 'postgres'
);

CREATE FOREIGN TABLE remote1 (
   x text
)
SERVER remote_server
OPTIONS (
schema_name 'public',
table_name 'remote1',
use_remote_estimate 'true'
);

create table local1 (x text);
\copy local1 from stdin
a
P
\.

select min(x) from local1;
select min(x) from remote1;

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers