Re: cache lookup failed for collation 0

On Thu, Apr 11, 2019 at 9:07 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com> writes:
> > Following test-sequence causing an error "cache lookup failed for
> collation 0";
> > postgres:5432 [42106]=# create table foobar(a bytea primary key, b int);
> > CREATE TABLE
> > postgres:5432 [42106]=# insert into foobar
> > values('\x4c835521685c46ee827ab83d376cf028', 1);
> > INSERT 0 1
> > postgres:5432 [42106]=# select * from foobar where a like '%1%';
> > ERROR: cache lookup failed for collation 0
>
> Good catch!
>
> > The error is coming from get_collation_isdeterministic() when colloid
> > passed is 0. I think like we do in get_collation_name(), we should return
> > false here when such collation oid does not exist.
>
> Considering that e.g. lc_ctype_is_c() doesn't fail for InvalidOid, I agree
> that it's probably a bad idea for get_collation_isdeterministic to fail.
> There's a lot of code that thinks it can check for InvalidOid only in slow
> paths. However, I'd kind of expect the default result to be "true" not
> "false". Doing what you suggest would make match_pattern_prefix fail
> entirely, unless we also put a special case there.
>

Do you mean, the code in get_collation_isdeterministic() should look like
something like below?

If colloid = InvalidOid then
return TRUE
ELSE IF tuple is valid then
return collisdeterministic from the tuple
ELSE
return FALSE

I think for non-zero colloid which is not valid we should return false, but
I may be missing your point here.

>
> regards, tom lane
>

--
Jeevan Chalke
Technical Architect, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company