[PATCH 3/5] index: optimize after all SQLite DB commits

[Eric Wong]

This covers v1 inboxes, as well.  We also guard the execution
since "PRAGMA optimize" was only introduced in SQLite 3.18.0
(2017-03-30)
---
 lib/PublicInbox/LeiMailSync.pm |  2 +-
 lib/PublicInbox/OverIdx.pm     |  1 +
 lib/PublicInbox/SearchIdx.pm   |  1 +
 lib/PublicInbox/V2Writable.pm  | 16 +++++-----------
 4 files changed, 8 insertions(+), 12 deletions(-)

diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm
index c6cd1bc58d0a..f7e37ad9ca80 100644
--- a/lib/PublicInbox/LeiMailSync.pm
+++ b/lib/PublicInbox/LeiMailSync.pm
@@ -48,7 +48,7 @@ sub lms_pause {
        my ($self) = @_;
        $self->{fmap} = {};
        my $dbh = delete $self->{dbh};
-       $dbh->do('PRAGMA optimize') if $dbh;
+       eval { $dbh->do('PRAGMA optimize') } if $dbh;
 }
 
 sub create_tables {
diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm
index d6d706f7fed0..9fdb26c0d5c2 100644
--- a/lib/PublicInbox/OverIdx.pm
+++ b/lib/PublicInbox/OverIdx.pm
@@ -434,6 +434,7 @@ sub commit_lazy {
        my ($self) = @_;
        delete $self->{txn} or return;
        $self->{dbh}->commit;
+       eval { $self->{dbh}->do('PRAGMA optimize') };
 }
 
 sub begin_lazy {
diff --git a/lib/PublicInbox/SearchIdx.pm b/lib/PublicInbox/SearchIdx.pm
index a2ed94993993..928152ec4df4 100644
--- a/lib/PublicInbox/SearchIdx.pm
+++ b/lib/PublicInbox/SearchIdx.pm
@@ -793,6 +793,7 @@ sub v1_checkpoint ($$;$) {
        ${$sync->{max}} = $self->{batch_bytes};
 
        $self->{mm}->{dbh}->commit;
+       eval { $self->{mm}->{dbh}->do('PRAGMA optimize') };
        my $xdb = $self->{xdb};
        if ($newest && $xdb) {
                my $cur = $xdb->get_metadata('last_commit');
diff --git a/lib/PublicInbox/V2Writable.pm b/lib/PublicInbox/V2Writable.pm
index efcc1fc21a18..3914383cc9d3 100644
--- a/lib/PublicInbox/V2Writable.pm
+++ b/lib/PublicInbox/V2Writable.pm
@@ -547,11 +547,11 @@ sub checkpoint ($;$) {
        }
        my $shards = $self->{idx_shards};
        if ($shards) {
-               my $mm = $self->{mm};
-               my $dbh = $mm->{dbh} if $mm;
+               my $dbh = $self->{mm}->{dbh} if $self->{mm};
 
                # SQLite msgmap data is second in importance
                $dbh->commit if $dbh;
+               eval { $dbh->do('PRAGMA optimize') };
 
                # SQLite overview is third
                $self->{oidx}->commit_lazy;
@@ -620,16 +620,10 @@ sub done {
                my $m = $err ? 'rollback' : 'commit';
                eval { $mm->{dbh}->$m };
                $err .= "msgmap $m: $@\n" if $@;
-               eval { $mm->{dbh}->do('PRAGMA optimize') };
-               $err .= "msgmap optimize: $@\n" if $@;
        }
-       if ($self->{oidx} && $self->{oidx}->{dbh}) {
-               if ($err) {
-                       eval { $self->{oidx}->rollback_lazy };
-                       $err .= "overview rollback: $@\n" if $@;
-               }
-               eval { $self->{oidx}->{dbh}->do('PRAGMA optimize') };
-               $err .= "overview optimize: $@\n" if $@;
+       if ($self->{oidx} && $self->{oidx}->{dbh} && $err) {
+               eval { $self->{oidx}->rollback_lazy };
+               $err .= "overview rollback: $@\n" if $@;
        }
 
        my $shards = delete $self->{idx_shards};
--
unsubscribe: one-click, see List-Unsubscribe header
archive: https://public-inbox.org/meta/