SQLite, UPDATE, and LIMIT

SQLite, UPDATE, and LIMIT

[Jonathan Corbet]

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.

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?

Thanks,

jon

From f0d6f3771013fadbeb8432533d51545696309ffb Mon Sep 17 00:00:00 2001
From: Jonathan Corbet <corbet@pi.lwn.net>
Date: Thu, 22 Feb 2018 23:05:46 +0000
Subject: [PATCH] Don't use LIMIT in UPDATE statements

...not all distributions build SQLite with that enabled.
---
 lib/PublicInbox/Msgmap.pm | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm
index a147b9f..6b6d1c6 100644
--- 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 = ?';
 		$dbh->do($sql, undef, $value, $key);
 	} else {
 		$sql = 'INSERT INTO meta (key,val) VALUES (?,?)';
-- 
2.14.3

Re: SQLite, UPDATE, and LIMIT

[Eric Wong]

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 (?,?)';

Re: SQLite, UPDATE, and LIMIT

[Jonathan Corbet]

On Thu, 22 Feb 2018 23:24:00 +0000
Eric Wong <e@80x24.org> wrote:

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

It seems to be.  I spent forever in this loop:

	while (it still doesn't work)
		install_next_perl_module()

I've stashed the list in an ansible config, so I can pass it youward if
you'd like to put it somewhere.

I'm just getting going, though; NNTP works but I've not tried HTTP yet.

Thanks,

jon

Re: SQLite, UPDATE, and LIMIT

[Eric Wong]

Jonathan Corbet <corbet@lwn.net> wrote:
> On Thu, 22 Feb 2018 23:24:00 +0000
> Eric Wong <e@80x24.org> wrote:
> 
> > So with your patch, is everything else good on Fedora?
> 
> It seems to be.  I spent forever in this loop:
> 
> 	while (it still doesn't work)
> 		install_next_perl_module()
> 
> I've stashed the list in an ansible config, so I can pass it youward if
> you'd like to put it somewhere.

Great to know!  A patch to INSTALL (currently Debian-oriented)
would be helpful.  Thanks for the interest.