Hi Ashutosh, Le ven. 6 oct. 2023 à 14:16, Ashutosh Bapat <ashutosh.bapat....@gmail.com> a écrit :
> Hi Jean-Christophe, > > On Fri, Sep 8, 2023 at 11:30 PM Jean-Christophe Arnu <jca...@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. > > There were similar discussions earlier. E.g. > > https://www.postgresql.org/message-id/flat/CACowWR1ARWyRepRxGfijMcsw%2BH84Dj8x2o9N3kvz%3Dz1p%2B6b45Q%40mail.gmail.com > . > > 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). Thanks again for your kind explanations Regards -- Jean-Christophe Arnu