[Csync2] recursive selects with sqlite

[Lars Ellenberg]

On Thu, Feb 19, 2009 at 09:15:36PM +0100, Art -kwaak- van Breemen wrote:
> Hello,
> 
> On Thu, Feb 19, 2009 at 08:27:13PM +0100, Christian Wiese wrote:
> > -------------------------snip------------------------------------------
> > # csync2 -rf /etc/opt/csync2
> > Database Error: Mark file as to be forced [1]: UPDATE dirty SET force =
> > 1 WHERE filename = '/etc/opt/csync2' UNION ALL SELECT filename from
> > file where filename > '/etc/opt/csync2/' and filename <
> > '/etc/opt/csync20'
> > -------------------------snip------------------------------------------
> 
> That's a weird statement! But I do recognize the UNION part.
> 
> > If I revert the change made by r403 it seems to be fine though!
> 
> I will check that piece. The query is obviously broken (I was
> probably to eager with fixing the selects), but it comes with a
> note: since we can't use the union (obviously), it might fall
> back to a pretty expensive update. The bigger your dirty table
> gets, the worse the update gets (and the locking time).


Index: csync2.c
===================================================================
--- csync2.c	(revision 402)
+++ csync2.c	(revision 403)
@@ -685,8 +685,8 @@
 					if ( !strcmp(realname, "/") )
 						asprintf(&where_rec, "or 1");
 					else
-						asprintf(&where_rec, "or (filename > '%s/' "
-							"and filename < '%s0')",
+						asprintf(&where_rec, "UNION ALL SELECT filename from file where filename > '%s/' "
+							"and filename < '%s0'",
 							url_encode(realname), url_encode(realname));
 				}

that should be:
+						asprintf(&where_rec, "UNION ALL SELECT filename from dirty where filename > '%s/' "
+							"and filename < '%s0'",


s/from file/from dirty/
right?
we are updating the _dirty_ table there.

> Anyway: the sqlite locking is even in my version still a pain. I
> guess that for high update and > M files setups we should look at a
> different approach for keeping syncs.

suggestions?

-- 
: Lars Ellenberg
: LINBIT | Your Way to High Availability
: DRBD/HA support and consulting http://www.linbit.com

DRBD® and LINBIT® are registered trademarks of LINBIT, Austria.