Re: current_schema will not use an text index ? - Mailing list pgsql-hackers

From Tom Lane
Subject Re: current_schema will not use an text index ?
Date
Msg-id 1584998.1642951002@sss.pgh.pa.us
Whole thread Raw
In response to current_schema will not use an text index ?  (Marcos Pegoraro <marcos@f10.com.br>)
List pgsql-hackers
Marcos Pegoraro <marcos@f10.com.br> writes:
> customer_schema text, --here is the problem, a text column.

> Until version 11 my select was using that index correctly. Then I´ve
> upgraded to 14.1, then ...

> explain analyze select customer_schema, pk from audit where customer_schema
> = current_schema and table_name =

"current_schema" is nowadays considered to have C collation, which is
appropriate for comparisons to columns in the system catalogs.  But that
causes your "customer_schema = current_schema" comparison to resolve as
having C input collation, which doesn't match the collation of your index
on customer_schema.  You could either change the query to look like

where customer_schema = current_schema collate "default" and ...

or else change the table so that customer_schema has "C" collation.

The reason the behavior changed is that we're less cavalier about
the collation of type "name" than we used to be.

            regards, tom lane



pgsql-hackers by date:

Previous
From: Julien Rouhaud
Date:
Subject: Re: Schema variables - new implementation for Postgres 15
Next
From: Tom Lane
Date:
Subject: Re: Warning in geqo_main.c from clang 13