[Csync2] recursive selects with sqlite

[Lars Ellenberg]

On Fri, Feb 20, 2009 at 11:03:27AM +0100, Lars Ellenberg wrote:
> 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.

nonsense.
cannot do a union select in an update ;)

> > 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?

should not be a problem for csync2 -fr, as the dirty table should be small
most of the time if you need to use that. no?

-- 
: 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.