pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedlocales and encodings Oh MY!
7+ messages / 5 participants
[nested] [flat]
* locales and encodings Oh MY!
@ 2010-11-12 02:31 mark <dvlhntr@gmail.com>
2010-11-12 07:45 ` Re: locales and encodings Oh MY! Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
0 siblings, 1 reply; 7+ messages in thread
From: mark @ 2010-11-12 02:31 UTC (permalink / raw)
To: pgsql-admin
Hi all,
So today while I investigating some slow queries I found out that some time
ago some of our databases (due, innocently I suspect, to upgrades or
hardware migrations) are now not using C as lc_ctype. And why some 'things'
got a lot slower...
To correct this problem it seems like I am going to have to do a dump,
intidb, then restore. (or add some new indexes and eat the extra overhead).
Unless someone knows something I don't about changing ctype and collation
with some sleight of hand (if you do I am very interested)
I have listed what I think I will be doing with regards to initdb. if anyone
sees problems with the following mixture during my dump -> init-> restore I
would be most keen in hearing about it.
initdb /path/to/data/dir --lc_ctype=C --lc_collation=C
--lc_message=en_US.UTF8 --lc_monetary=en_US.UTF8 --lc_numeric=en_US.UTF8
--lc_time=en_US.UTF8 -E UTF8
Thank you,
Mark
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: locales and encodings Oh MY!
2010-11-12 02:31 locales and encodings Oh MY! mark <dvlhntr@gmail.com>
@ 2010-11-12 07:45 ` Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-11-12 16:27 ` Re: locales and encodings Oh MY! mark <dvlhntr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Gabriele Bartolini @ 2010-11-12 07:45 UTC (permalink / raw)
To: mark <dvlhntr@gmail.com>; +Cc: pgsql-admin
Hi Mark,
Il 12/11/10 03:31, mark ha scritto:
> I have listed what I think I will be doing with regards to initdb. if anyone
> sees problems with the following mixture during my dump -> init-> restore I
> would be most keen in hearing about it.
FYI, PostgreSQL 8.4 introduced database level collation. Before that,
yes, you need to re-init your data dir.
> initdb /path/to/data/dir --lc_ctype=C --lc_collation=C
> --lc_message=en_US.UTF8 --lc_monetary=en_US.UTF8 --lc_numeric=en_US.UTF8
> --lc_time=en_US.UTF8 -E UTF8
Maybe you meant --lc_collate ?
Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: locales and encodings Oh MY!
2010-11-12 02:31 locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 07:45 ` Re: locales and encodings Oh MY! Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
@ 2010-11-12 16:27 ` mark <dvlhntr@gmail.com>
2010-11-12 18:53 ` Re: [HACKERS] locales and encodings Oh MY! Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: mark @ 2010-11-12 16:27 UTC (permalink / raw)
To: Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it>; +Cc: pgsql-admin; pgsql-hackers@postgresql.org
On Fri, Nov 12, 2010 at 12:45 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:
> Hi Mark,
>
> Il 12/11/10 03:31, mark ha scritto:
>>
>> I have listed what I think I will be doing with regards to initdb. if
>> anyone
>> sees problems with the following mixture during my dump -> init->
>> restore I
>> would be most keen in hearing about it.
>
> FYI, PostgreSQL 8.4 introduced database level collation. Before that, yes,
> you need to re-init your data dir.
Guess I missed that, I have 8.3.X boxes in production and 9.0.1 boxes
in dev so I guess only someone of them will require a re-init.
>>
>> initdb /path/to/data/dir --lc_ctype=C --lc_collation=C
>> --lc_message=en_US.UTF8 --lc_monetary=en_US.UTF8 --lc_numeric=en_US.UTF8
>> --lc_time=en_US.UTF8 -E UTF8
>
> Maybe you meant --lc_collate ?
>
Yes I did mean lc_collate - thanks
> Cheers,
> Gabriele
>
With how similar straight C and en_US.UTF8 are it was suggested to me,
by persons who are far more C knowledgeable then I in my office, that
this is something the PG community could "fix" . A "fix" being so that
"col LIKE 'foo%' " could use btree indexes in locales like en_US.UTF8
(and probably some others).
@hackers ->
is the request unreasonable ? anyone got any idea of the price tag to
make that happen ?
> --
> Gabriele Bartolini - 2ndQuadrant Italia
> PostgreSQL Training, Services and Support
> gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [HACKERS] locales and encodings Oh MY!
2010-11-12 02:31 locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 07:45 ` Re: locales and encodings Oh MY! Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-11-12 16:27 ` Re: locales and encodings Oh MY! mark <dvlhntr@gmail.com>
@ 2010-11-12 18:53 ` Robert Haas <robertmhaas@gmail.com>
2010-11-12 19:03 ` Re: [HACKERS] locales and encodings Oh MY! Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Robert Haas @ 2010-11-12 18:53 UTC (permalink / raw)
To: mark <dvlhntr@gmail.com>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it>; pgsql-admin; pgsql-hackers@postgresql.org
> With how similar straight C and en_US.UTF8 are it was suggested to me,
> by persons who are far more C knowledgeable then I in my office, that
> this is something the PG community could "fix" . A "fix" being so that
> "col LIKE 'foo%' " could use btree indexes in locales like en_US.UTF8
> (and probably some others).
>
>
> @hackers ->
> is the request unreasonable ? anyone got any idea of the price tag to
> make that happen ?
I thought it already did that.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [HACKERS] locales and encodings Oh MY!
2010-11-12 02:31 locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 07:45 ` Re: locales and encodings Oh MY! Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-11-12 16:27 ` Re: locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 18:53 ` Re: [HACKERS] locales and encodings Oh MY! Robert Haas <robertmhaas@gmail.com>
@ 2010-11-12 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-12 19:17 ` Re: [HACKERS] locales and encodings Oh MY! mark <dvlhntr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2010-11-12 19:03 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: mark <dvlhntr@gmail.com>; Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it>; pgsql-admin; pgsql-hackers@postgresql.org
Robert Haas <robertmhaas@gmail.com> writes:
>> With how similar straight C and en_US.UTF8 are it was suggested to me,
>> by persons who are far more C knowledgeable then I in my office, that
>> this is something the PG community could "fix" . A "fix" being so that
>> "col LIKE 'foo%' " could use btree indexes in locales like en_US.UTF8
>> (and probably some others).
>> is the request unreasonable ? anyone got any idea of the price tag to
>> make that happen ?
> I thought it already did that.
No, and the odds of it ever happening are insignificant. The sort order
associated with en_US (and other "dictionary order" locales) is just too
randomly different from what you need to optimize a LIKE search.
(Whoever told you en_US sorts similarly to C is nuts.)
The solution if you want the database's prevailing sort order to be en_US
is to put an extra text_pattern_ops index on the column you want to do
LIKE searches on. We might eventually have the ability to spell that
"put a C-locale index on the column", but text_pattern_ops is the way to
do it today.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [HACKERS] locales and encodings Oh MY!
2010-11-12 02:31 locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 07:45 ` Re: locales and encodings Oh MY! Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-11-12 16:27 ` Re: locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 18:53 ` Re: [HACKERS] locales and encodings Oh MY! Robert Haas <robertmhaas@gmail.com>
2010-11-12 19:03 ` Re: [HACKERS] locales and encodings Oh MY! Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-11-12 19:17 ` mark <dvlhntr@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: mark @ 2010-11-12 19:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it>; pgsql-admin; pgsql-hackers@postgresql.org
On Fri, Nov 12, 2010 at 12:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>>> With how similar straight C and en_US.UTF8 are it was suggested to me,
>>> by persons who are far more C knowledgeable then I in my office, that
>>> this is something the PG community could "fix" . A "fix" being so that
>>> "col LIKE 'foo%' " could use btree indexes in locales like en_US.UTF8
>>> (and probably some others).
>
>>> is the request unreasonable ? anyone got any idea of the price tag to
>>> make that happen ?
>
>> I thought it already did that.
>
> No, and the odds of it ever happening are insignificant. The sort order
> associated with en_US (and other "dictionary order" locales) is just too
> randomly different from what you need to optimize a LIKE search.
> (Whoever told you en_US sorts similarly to C is nuts.)
>
> The solution if you want the database's prevailing sort order to be en_US
> is to put an extra text_pattern_ops index on the column you want to do
> LIKE searches on. We might eventually have the ability to spell that
> "put a C-locale index on the column", but text_pattern_ops is the way to
> do it today.
>
> regards, tom lane
>
Ok I hear you loud and clear. I am going to eat the overhead until
I get to 9.0.1, currently on 8.3.X in some places.
I will either take an outage and do a dump - re-init-restore or
inplace upgrade and then do some locking, copy, drop old, rename new
db path.
thanks all.
..: Mark
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: locales and encodings Oh MY!
@ 2010-11-12 17:58 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-11-12 17:58 UTC (permalink / raw)
To: gabriele.bartolini@2ndquadrant.it; dvlhntr@gmail.com; +Cc: pgsql-admin; pgsql-hackers@postgresql.org
mark wrote:
> A "fix" being so that "col LIKE 'foo%' " could use btree indexes in
> locales like en_US.UTF8 (and probably some others).
How about specifying an opclass?:
http://www.postgresql.org/docs/current/interactive/indexes-opclass.html
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-11-12 19:17 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-12 02:31 locales and encodings Oh MY! mark <dvlhntr@gmail.com>
2010-11-12 07:45 ` Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-11-12 16:27 ` mark <dvlhntr@gmail.com>
2010-11-12 18:53 ` Robert Haas <robertmhaas@gmail.com>
2010-11-12 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-12 19:17 ` mark <dvlhntr@gmail.com>
2010-11-12 17:58 Re: locales and encodings Oh MY! Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox