=?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
> I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -
Yeah :-(. I traced through this, and it seems it's a collation
problem. Internally, that call looks like
extract('year'::text, date_column)
The text constant is marked as having collation "default", which means
that extract() is marked as having input collation "default", and then
it falls foul of this rule:
* ... An expression is considered safe to send
* only if all operator/function input collations used in it are traceable to
* Var(s) of the foreign table. That implies that if the remote server gets
* a different answer than we do, the foreign table's columns are not marked
* with collations that match the remote table's columns, which we can
* consider to be user error.
Of course, extract() doesn't actually care about collation, but
postgres_fdw has no good way to know that. Nor does it trust the
remote server to have the same set of collations the local one does,
so it doesn't want to try to fix this by sending explicit COLLATE
clauses.
Somebody ought to work on improving that mess sometime. One thought
that comes to mind is to have a server option authorizing postgres_fdw
to believe that all local collations exist on the remote side.
regards, tom lane