Tonight, I was contacted by jrm on IRC, who told me that FreshPorts was missing something on a search. Yep. It was not in there.
I looked on dev and I saw the same thing.
Finding the cause
I started by enabling debug on dev. I found this SQL:
SELECT count(*) FROM ports P LEFT OUTER JOIN ports_vulnerable PV ON PV.port_id = P.id LEFT OUTER JOIN commit_log CL ON P.last_commit_id = CL.id LEFT OUTER JOIN repo R ON CL.repo_id = R.id LEFT OUTER JOIN commit_log_branches CLB ON CL.id = CLB.commit_log_id JOIN system_branch SB ON SB.branch_name = 'head' AND SB.id = CLB.branch_id, categories C, element E WHERE P.category_id = C.id AND P.element_id = E.id AND E.name ILIKE '%en-aspell%' and E.status = 'A'
I started by removing one LEFT OUTER JOIN at a time until I found the cause. It was SB.id = CLB.branch_id.
OK, but why?
Let’s look at some of the SQL first.
One line 3, we start with P.last_commit_id, which is the last commit for this port.
From there, we join to the commit_log table to find the repo_id for that commit on line 4.
Line 5 gives us the commit_log_branch table entry for that commit, and we know now which branch this commit touched.
Lines 6 and 7 will restrict the search to head.
I started examining the values from these JOINs. I discovered that some commits did not have entries in the commit_log_branches table. These were commits which occurred before that table was created.
The problem arose when a port whose last commit occurred before the creation of that table. These ports would never be included because, according to the database, there was no such commit on head for them.
The solution is to add entries to the commit_log_branches table. The following SQL accomplished that.
insert into commit_log_branches SELECT distinct P.last_commit_id, (select SB.id FROM system_branch SB where branch_name = 'head') FROM ports P LEFT OUTER JOIN commit_log CL ON P.last_commit_id = CL.id WHERE NOT EXISTS (SELECT * FROM commit_log_branches CLB WHERE CL.id = CLB.commit_log_id) and P.last_commit_id is not null;
While there, I did this for good measure:
create unique index commit_log_branches_pk on commit_log_branches(commit_log_id, branch_id);
The table now looks like this:
freshports.org=# \d commit_log_branches Table "public.commit_log_branches" Column | Type | Modifiers ---------------+---------+----------- commit_log_id | integer | not null branch_id | integer | not null Indexes: "commit_log_branches_pk" UNIQUE, btree (commit_log_id, branch_id) Foreign-key constraints: "commit_log_branch_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES system_branch(id) ON UPDATE CASCADE ON DELETE CASCADE "commit_log_branch_commit_log_id_fkey" FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE freshports.org=#