On Fri, Oct 27, 2017 at 11:00:14PM +0100, James Le Cuirot wrote:
> On Mon, 23 Oct 2017 13:23:51 +0200
> Lars Ellenberg <lars.ellenb...@linbit.com> wrote:
> My locale and collation is entirely en_GB.UTF-8.
> > afaik, = < > compare "collate character units", while LIKE, appart from
> > supporting wildcards, compares "byte per byte" when used with literals.
> > I may be wrong.
> > Anyways, maybe alter table ... binary ... helps?
> > Or change "TEXT" to "BLOB"?
> > Something like that?
> This gives the right result:
> csync2=> SELECT filename from file where filename::bytea >
> '/mnt/csync2/portage/app-editors/ng/' and filename::bytea <
> '/mnt/csync2/portage/app-editors/ng0' ORDER BY filename;
> Presumably this is comparing "byte for byte" as you say LIKE does but
> then why not just use LIKE in the first place?
"probably" (code is / was like that since over ten years)
at some point it was thought (or even measured) to help with
performance (presumably back when it was sqlite2 only).
Maybe that can be re-evaluated.
Also we probably should
CREATE TABLE ... ( ... TEXT COLLATE "C" NOT NULL ...)
Or use BYTEA / BLOB. Not sure which option is better.
You want to check what "select 'ABC' = 'abc';"
returns for various locales/collations,
and then double check for your favorite
umlauts or accented characters...
> > I don't think many installations out there use csync2 with mysql or
> > postgres,
> > so it is very likely that there are a number of shortcomings burried in
> > there,
> > I'd expect "very long path names" to show problems as well, where "very
> > long" may be simply "longer than the default supported unique key length
> > on text fields.
> I figured that few people use this, especially since you can't specify
> the database in the configuration file.
Supposedly you just put something like this into the config file:
optionally with full database name.
Same string you'd put after the "-D" command line switch.
> I happen to have PostgreSQL running on both nodes anyway and thought
> it might be faster than SQLite.
If you are willing to do some benchmarks,
let us know the results.
In "normal usage" of csync2, I doubt that the database
would ever become the bottleneck.
> I gather the unique key length varies but is generally around 2000-3000
> characters. I don't expect to have paths that long but thanks for the
> heads up. Does SQLite not have such a limit? I couldn't find any
> reference to one.
No, sqlite apparently does not care for text field length there.
: Lars Ellenberg
: LINBIT | Keeping the Digital World Running
: DRBD -- Heartbeat -- Corosync -- Pacemaker
: R&D, Integration, Ops, Consulting, Support
DRBD® and LINBIT® are registered trademarks of LINBIT
Csync2 mailing list