Re: Set COLLATE on a session level

--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Am 06.12.20, 06:15 schrieb "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at>:

On Sat, 2020-12-05 at 13:12 +0100, Peter Eisentraut wrote:
> On 2020-12-04 17:18, Tom Lane wrote:
> > > There is a SET COLLATION command in the SQL standard that does this.
> > > Someone just has to implement it. It wouldn't be terribly difficult, I
> > > think.
> >
> > [ squint... ] Just because it's in the standard doesn't mean it's a
> > good idea. It sounds like this is morally equivalent to a GUC that
> > changes query semantics. We have learned painfully that such behaviors
> > are best avoided, because they break things you didn't expect to break.
>
> I think it would be analogous to the schema search path.

Usually you notice right away if the "search_path" is wrong, because
relations won't be found.

But with a bad collation set in a session, the problems would be more
subtle. For example, if someone asks you why an index isn't used to
support sorting, you'd always have to remember to ask what collation
has been set in the session.

This is true, but it is already the case in Oracle. There it is usually even the case that an index on a VARCHAR2 column is not used for sorting, since the index is binary sorted, but the language setting is usually not.

The SET COLLATION command would be exactly what we're looking for. (

BR
Dirk