Port deactivate boost hangs forever

[Clemens Lang]

Hey,

----- On 13 Nov, 2014, at 13:27, Ryan Schmidt ryandesign at macports.org wrote:

> Sure...
> 
>> DROP TABLE sqlite_stat1;
>> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active;
> 
> 0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?)
> 
>> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND
>> id=?;
> 
> 0|0|0|SEARCH TABLE files USING INDEX file_path (path=?)
> 
>> ANALYZE;
>> SELECT * FROM sqlite_stat1;
> 
>> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active;
> 
> 0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?)
> 
>> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND
>> id=?;
> 
> 0|0|0|SEARCH TABLE files USING INDEX file_path (path=?)

OK, that was a red herring, mostly because I didn't also instruct you to drop
the sqlite_stat4 table as well and re-open the sqlite3 tool. See this:

With table metadata:

sqlite> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active;
0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?)
sqlite> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND id=?;
0|0|0|SEARCH TABLE files USING INDEX file_path (path=?)

Now:
sqlite> DROP TABLE sqlite_stat1;
sqlite> DROP TABLE sqlite_stat4;
sqlite> ^D
:( clemens at cSchlepptop:~$ sudo sqlite3 /opt/local/var/macports/registry/registry.db
SQLite version 3.8.7 2014-10-17 11:24:17
Enter ".help" for usage hints.
sqlite> .load /Users/clemens/Development/MacPortsBase/src/cregistry/macports.sqlext
sqlite> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active;
0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?)
sqlite> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND id=?;
0|0|0|SEARCH TABLE files USING INDEX file_port (id=?)

And there's the problem – the query that updates the active state in the database
doesn't use the file_path index anymore, but instead the file_port index that maps
the registered files to ports. Of course, the file_port index has the same value
for a lot rows (all belonging to the same port) and is thus a bad index to be used
for this query. In essence each update of a file while activating boost selects all
files installed by boost and then goes through them one by one to find the one with
the right path, possibly doing a strcmp() on each row. Unsurprisingly, this is slow.

I assume the same will be the case for deactivation. We should find a way to force
the usage of the file_path index with these queries.

-- 
Clemens Lang