[Csync2] csync_check_del broken under PostgreSQL

James Le Cuirot chewi at gentoo.org
Sun Oct 22 12:33:55 CEST 2017


When checking for deleted files recursively, csync2 issues a SQL
statement like this.

=> SELECT filename from file where filename > '/mnt/csync2/portage/app-editors/ng/' and filename < '/mnt/csync2/portage/app-editors/ng0' ORDER BY filename;
 filename 
----------
(0 rows)

This never returns anything under PostgreSQL. The behaviour is actually
rather odd. Using LIKE gives the result we expect.

=> SELECT filename FROM file WHERE filename LIKE '/mnt/csync2/portage/app-editors/ng/%';
                               filename                               
----------------------------------------------------------------------
 /mnt/csync2/portage/app-editors/ng/metadata.xml
 /mnt/csync2/portage/app-editors/ng/files
 /mnt/csync2/portage/app-editors/ng/files/ng-1.5beta1-ncurses.patch
 /mnt/csync2/portage/app-editors/ng/files/ng-1.5beta1-configure.patch
 /mnt/csync2/portage/app-editors/ng/Manifest
 /mnt/csync2/portage/app-editors/ng/ng-1.5_beta1-r2.ebuild
(6 rows)

But watch what happens when we change the 0 to a g.

=> SELECT filename from file where filename > '/mnt/csync2/portage/app-editors/ng/' and filename < '/mnt/csync2/portage/app-editors/ngg' ORDER BY filename;
                               filename                               
----------------------------------------------------------------------
 /mnt/csync2/portage/app-editors/ng/files
 /mnt/csync2/portage/app-editors/ng/files/ng-1.5beta1-configure.patch
 /mnt/csync2/portage/app-editors/ng/files/ng-1.5beta1-ncurses.patch
(3 rows)

It's almost like the / in ng/files isn't used in the comparison. I
couldn't find anything in the PostgreSQL documentation specifically
about using the <> operators with strings.

I don't know why LIKE wasn't used here. Perhaps it was for performance?
I'm dubious whether there's much difference but maybe you benchmarked
it. Or perhaps it was for case-sensitivity? PostgreSQL is
case-sensitive anyway and so is SQLite by default. MySQL can be made to
do a case-sensitive comparison using LIKE BINARY.

-- 
James Le Cuirot (chewi)
Gentoo Linux Developer
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 981 bytes
Desc: OpenPGP digital signature
URL: <http://lists.linbit.com/pipermail/csync2/attachments/20171022/eae86512/attachment.pgp>


More information about the Csync2 mailing list