From mboxrd@z Thu Jan  1 00:00:00 1970
Return-Path: <e@80x24.org>
X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on dcvr.yhbt.net
X-Spam-Level: 
X-Spam-ASN:  
X-Spam-Status: No, score=-4.0 required=3.0 tests=ALL_TRUSTED,BAYES_00
	shortcircuit=no autolearn=ham autolearn_force=no version=3.4.0
Received: from localhost (dcvr.yhbt.net [127.0.0.1])
	by dcvr.yhbt.net (Postfix) with ESMTP id 32A8A1F404;
	Thu, 22 Feb 2018 23:24:00 +0000 (UTC)
Date: Thu, 22 Feb 2018 23:24:00 +0000
From: Eric Wong <e@80x24.org>
To: Jonathan Corbet <corbet@lwn.net>
Cc: meta@public-inbox.org
Subject: Re: SQLite, UPDATE, and LIMIT
Message-ID: <20180222232400.GA25726@whir>
References: <20180222161414.24d305f1@lwn.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <20180222161414.24d305f1@lwn.net>
List-Id: <meta.public-inbox.org>

Jonathan Corbet <corbet@lwn.net> wrote:
> So I'm messing with public-inbox a bit.  I had to make the following
> change before things would work on my Fedora system.  (This is with
> v1.0.0).
> 
> The symptom was errors out of SQLite; it wouldn't pass "make test" much
> less work properly.  Some digging turns up this:
> 
> 	If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> 	compile-time option then the syntax of the UPDATE statement is
> 	extended with optional ORDER BY and LIMIT clauses as follows
> 
> Evidently, Fedora does not enable that option.

Odd; I didn't know about that option and don't know if there's
performance implications for this.

So with your patch, is everything else good on Fedora?

> Now, I assume that LIMIT is there for a reason; I've not even tried to
> look at the schema that public-inbox uses yet.  But I thought I would toss
> this out there to see you thought.  Perhaps what I really need to do is to
> ask Fedora to turn that option on?

Using LIMIT is probably just a habit of mine.

> --- a/lib/PublicInbox/Msgmap.pm
> +++ b/lib/PublicInbox/Msgmap.pm
> @@ -57,7 +57,7 @@ sub meta_accessor {
>  	$prev = $dbh->selectrow_array($sql, undef, $key);
>  
>  	if (defined $prev) {
> -		$sql = 'UPDATE meta SET val = ? WHERE key = ? LIMIT 1';
> +		$sql = 'UPDATE meta SET val = ? WHERE key = ?';

`key' is the primary key anyways, so it should be fine to remove
LIMIT, there.  Will apply.

>  		$dbh->do($sql, undef, $value, $key);
>  	} else {
>  		$sql = 'INSERT INTO meta (key,val) VALUES (?,?)';