Re: jsonb, collection & postgres_fdw - Mailing list pgsql-hackers
From | Konstantin Knizhnik |
---|---|
Subject | Re: jsonb, collection & postgres_fdw |
Date | |
Msg-id | [email protected] Whole thread Raw |
In response to | Re: jsonb, collection & postgres_fdw (Tom Lane <[email protected]>) |
Responses |
Re: jsonb, collection & postgres_fdw
|
List | pgsql-hackers |
On 13.08.2020 20:00, Tom Lane wrote: > Konstantin Knizhnik <[email protected]> writes: >> Right now jsonb functions are treated as non-shippable by postgres_fdw >> and so predicates with them are not pushed down to foreign server: > Yeah, that's kind of annoying, but breaking the collation check > is not an acceptable fix. And what you're proposing *does* break it. > The issue here is that the function's input collation is coming from > the default collation applied to the text constant, and we can't assume > that that will be the same on the remote side. > > In reality, of course, jsonb_exists doesn't care about its input collation > --- but postgres_fdw has no way to know that. I don't see any easy way > around that. > > One idea that would probably work in a lot of postgres_fdw usage scenarios > is to have a foreign-server-level flag that says "all the collations on > that server behave the same as the local ones, and the default collation > is the same too", and then we just skip the collation checking altogether. > But I'm a bit worried that if someone mistakenly sets that flag, the > misbehavior will be very hard to detect. > > regards, tom lane Thank you for clarification. And sorry for mistyping in topic (there should be "collation" instead of "collection"). Actually I do not know much about handling collations in Postgres and particularly in postgres_fdw. Can you (or somebody else) provide more information about this fragment of code: /* * If function's input collation is not derived from a foreign * Var, it can't be sent to remote. */ if (fe->inputcollid == InvalidOid) /* OK, inputs are all noncollatable */ ; else if (inner_cxt.state != FDW_COLLATE_SAFE || fe->inputcollid != inner_cxt.collation) return false; So we have function call expression which arguments have associated collation, but function itself is collection-neutral: funccollid = 0 Why it is not safe to push this function call to the remote server? Why it breaks collation check? If there are some unsafe operations with collations during argument evaluation, then we detect it while recursive processing of arguments. I agree that my proposed fix is not correct. But what about this check: if (fe->inputcollid == InvalidOid) /* OK, inputs are all noncollatable */ ; else if (fe->funccollid == InvalidOid) /* OK, function is noncollatable */ ; Or funccollid=0 doesn't mean that collations of function arguments do not affect function behavior?
pgsql-hackers by date: