On Fri, Sep 8, 2023 at 11:30 PM Jean-Christophe Arnu <jcarnu@gmail.com> wrote: > > Maybe we could add another condition to the first if statement in order to allow a “no-collation” function to be pushed down even if they have “collatable” parameters. I’m not sure about the possible regressions of behaviour of this change, but it seems to work fine with date_trunc() and date_part() (which suffers the same problem).
That may not work since the output of the function may be dependent upon the collation on the inputs.
Reading Tom's first reply there you may work around this by declaring the collation explicitly.
Thanks for your reply. I did not catch these messages in the archive. Thanks for spotting them.
Briefly reading Tom's reply, the problem seems to be trusting whether the default collation locally and on the foreign server respectively is same or not. May be a simple fix is to declare a foreign server level option declaring that the default collation on the foreign server is same as the local server may be a way to move forward. But given that the problem remains unsolved for 7 years at least, may be such a simple fix is not enough.
I studied postgres_fdw source code a bit and the problem is not as easy to solve : one could set an option telling the default remote collation is aligned with local per "server" but nothing guaranties that the parameter collation is known on the «remote» side.
Another solution would be to attach another attribute to a function indicating whether the output of that function depends upon the input collations or not. Doing that just for FDW may not be acceptable though.
Yes, definitely. I thought
Anyway, you're right, after 7 years, this is a really difficult problem to solve and there's no straightforward solution (to my eyes).