5 ms·
Is that true about the broken Unicode support? I was going to try Postgres asap, but this would be a dealbreaker. I can hardly imagine it to be the case, though
by Tichy 18y ago
Is that true about the broken Unicode support? I was going to try Postgres asap, but this would be a dealbreaker. I can hardly imagine it to be the case, though?
- ubernostrum 18y agoI don't really know what that comment was about. I've been using Postgres with a thoroughly-Unicode application stack (Django) for quite some time and never had a problem...
- fauigerzigerk 18y agoWhich locale did you use to initialise your environment? It may well be that you haven't run into the LIKE + indexes issue or didn't notice it because the table didn't have a lot of data in it. By the way, do you realise that Django's ORM does not support optimistic locking in a transactionally safe way?
- ubernostrum 18y ago"It may well be that you haven't run into the LIKE + indexes issue or didn't notice it because the table didn't have a lot of data in it." I dunno, the backups (pgdump -> bzip), last time I looked at one, were over 6GB, so I'd say there's some data in there. I've just never seen Unicode-related issues. "By the way, do you realise that Django's ORM does not support optimistic locking in a transactionally safe way?" It doesn't really expose locking, period; consult the many threads on the dev list to find out why.
- fauigerzigerk 18y agoThe problem is that for locales other than the "C" locale the LIKE operator will not use indexes unless you explicitly tell postgres to use text_pattern_ops. However, if you do that other comparison operators will not use that index. Effectively, what you have to do is to use two indexes on the same column, one using text_pattern_ops and one using the default operator class. I know this is not a bug so it's debatable whether it should be called broken. Let me call it a broken design. [Edit] And there's another workaround that allows you to avoid using two indexes. You can use text_pattern_ops and use regular expressions for all comparisons, even for equality. This solution may have other performance drawbacks. I'm not sure.
- Tichy 17y agoTrying to understand the issue: I suppose text_pattern_ops is a special kind of index optimized for LIKE searches? Then why should other queries use that index? Serious question, since you call the design broken - but what would be the proper way to do it in your opinion?