Today, while I wasn’t looking, both dev and test located and imported new package information for:
- FreeBSD:11:amd64
- FreeBSD:11:i386
- FreeBSD:12:amd64
- FreeBSD:12:i386
- FreeBSD:13:amd64
- FreeBSD:13:i386
However, the packages table was not correctly updated.
Why? Because I lost some function code I wrote.
This is the code which was in use, as mentioned in Using DEFERRABLE INITIALLY DEFERRED on constraints:
UPDATE packages_raw PR
SET abi_id = abi.id,
port_id = P.id
FROM abi, ports P, element_pathname EP
WHERE PR.abi = 'FreeBSD:13:aarch64'
AND PR.package_set = 'latest'
AND PR.abi = abi.name
AND EP.pathname = '/ports/head/' || PR.package_origin
AND P.element_id = EP.element_id;
As you can see, this will only update the FreeBSD:13:aarch64 ABI, for both latest and quarterly repos.
I needed the parameter driven version. Sadly, the above was in the repo, as expected. Looking back through the change log, it had never been saved.
I started thinking about backups I had taken of the database. As I was getting ready to restore old versions, I thought about where I might have saved this away during the work-in-progress phase.
I found this in an old explain:
UPDATE packages_raw PR
SET abi_id = abi.id,
port_id = PO.port_id
FROM abi, ports_origin PO, ports P, element_pathname EP
WHERE PR.abi = 'FreeBSD:13:aarch64'
AND PR.package_set = 'latest'
AND PR.abi = abi.name
AND PO.port_origin = PR.package_origin
AND PO.port_id = P.id
AND P.element_id = EP.element_id
AND EP.pathname like '/ports/head/%';
I remembered removing the ports_origin table from the query. This is what I massaged the above into:
-- set abi_id and port_id before updating the pacakges table.
UPDATE packages_raw PR
SET abi_id = abi.id,
port_id = P.id
FROM abi, ports P, element_pathname EP
WHERE abi.id = l_abi_id
AND abi.name = PR.abi
AND PR.package_set = a_package_set
AND EP.pathname = '/ports/head/' || PR.package_origin
AND P.element_id = EP.element_id;
While hunting around old auto-saved TextEdit files, I found something which was identical to the above.
Here’s the final diff:
[dan@pg02:~/src/freshports/database-schema] $ svn di
Index: sp.txt
===================================================================
--- sp.txt (revision 5312)
+++ sp.txt (working copy)
@@ -3715,9 +3715,9 @@
SET abi_id = abi.id,
port_id = P.id
FROM abi, ports P, element_pathname EP
- WHERE PR.abi = 'FreeBSD:13:aarch64'
- AND PR.package_set = 'latest'
- AND PR.abi = abi.name
+ WHERE abi.id = l_abi_id
+ AND abi.name = PR.abi
+ AND PR.package_set = a_package_set
AND EP.pathname = '/ports/head/' || PR.package_origin
AND P.element_id = EP.element_id;
[dan@pg02:~/src/freshports/database-schema] $
But wait, there’s more
While proof-reading this, I realized that I can remove abi from the above query.
We already have l_abi_id (line 13) and abi.name (line 12). Let’s try this instead:
UPDATE packages_raw PR
SET abi_id = l_abi_id,
port_id = P.id
FROM ports P, element_pathname EP
WHERE PR.abi = a_abi_name
AND PR.package_set = a_package_set
AND EP.pathname = '/ports/head/' || PR.package_origin
AND P.element_id = EP.element_id;
The explain analyse for this query is still good.
What’s next?
The packages cache needs to be automatically cleared after importing. At present, that’s a manual command:
[dan@test-nginx01:~] $ sudo rm -rf ~freshports/cache/packages/* [dan@test-nginx01:~] $
The UpdatePackagesFromRawPackages.py script should raise a new signal.
No, it can’t be a signal. Signals are only within a given host. I has to be through the database because that’s the only real communication between the ingress host and the website.
Todays’s lessons
When you’ve got something good, make sure it’s saved to the repo.
