This post is mostly me putting thoughts down as I work through this problem.
As part of fixing a recent issue, I want to manually trigger the importing of a new package repo into FreshPorts.
The check for new repos is performed by this periodic script:
Aug 17 01:00:06 dvl-ingress01 FreshPorts[80429]: into /usr/local/etc/periodic/hourly/900.freshports-check-repos-for-new-builds
That shell script launches this Python script:
Aug 17 01:00:06 dvl-ingress01 check_repos_for_new_stuff.py[80434]: Starting up
The list of repos to review is provided by this database query:
curs.execute("SELECT * from GetReposToReview()")
In short, that’s the list of entries in the abi table with either latest or quarterly appended.
The script then grabs the last-modified field of the file at:
https://pkg.freebsd.org/$ABI/$BRANCH/packagesite.txz
The script invokes this stored procedure, which updates the packages_last_checked.last_checked field, so we know when we last checked this ABI/BRANCH combination.
The return values lets the script know if this is a fresher repo than the one most last imported.
check_repos_for_new_stuff.py then sets two flags:
- SIGNAL_NEW_REPO_READY_FOR_IMPORT ( /var/db/freshports/signals/new_repo_ready_for_import)
- SIGNAL_JOB_WAITING (/var/db/freshports/signals/job_waiting)
The second flag tells ingress.sh that it should invoke the job_waiting.pl script. The first flag tells job_waiting.pl what work to perform.
So, what do I do
Let’s look at that table. I want to reimport FreeBSD:13:i386/quarterly.
freshports.dvl=# select name, PLC.*
from packages_last_checked PLC join ABI on abi.id = PLC.abi_id
order by repo_date desc nulls last, name, package_set;
name | abi_id | package_set | last_checked | repo_date | import_date | processed_date
------------------------+--------+-------------+-------------------------------+------------------------+-------------------------------+-------------------------------
FreeBSD:13:i386 | 10 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-08-16 22:05:01+00 | 2024-08-17 01:00:28.929512+00 | 2024-08-17 01:00:35.720471+00
FreeBSD:13:amd64 | 11 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-08-16 11:10:43+00 | 2024-08-16 14:00:27.359006+00 | 2024-08-16 14:00:34.002351+00
FreeBSD:13:i386 | 10 | latest | 2024-08-17 14:00:07.553081+00 | 2024-08-16 03:08:44+00 | 2024-08-16 06:00:39.20098+00 | 2024-08-16 06:00:45.900669+00
FreeBSD:13:amd64 | 11 | latest | 2024-08-17 14:00:07.553081+00 | 2024-08-16 03:06:10+00 | 2024-08-16 06:00:25.360602+00 | 2024-08-16 06:00:32.390262+00
FreeBSD:15:amd64 | 47 | latest | 2024-08-17 14:00:07.553081+00 | 2024-08-15 23:16:05+00 | 2024-08-16 05:00:29.47855+00 | 2024-08-16 05:00:37.082446+00
FreeBSD:14:i386 | 30 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-08-15 18:58:09+00 | 2024-08-15 20:00:25.357084+00 | 2024-08-15 20:00:31.639508+00
FreeBSD:14:i386 | 30 | latest | 2024-08-17 14:00:07.553081+00 | 2024-08-15 16:38:55+00 | 2024-08-15 19:00:33.29832+00 | 2024-08-15 19:00:48.876511+00
FreeBSD:14:amd64 | 27 | latest | 2024-08-17 14:00:07.553081+00 | 2024-08-15 14:47:59+00 | 2024-08-15 19:00:25.325638+00 | 2024-08-15 19:00:40.280413+00
FreeBSD:14:amd64 | 27 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-08-15 12:48:12+00 | 2024-08-15 14:00:26.461217+00 | 2024-08-15 14:00:48.926302+00
FreeBSD:14:armv7 | 29 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-07-31 15:21:26+00 | 2024-08-01 03:00:27.690114+00 | 2024-08-01 03:00:33.601803+00
FreeBSD:15:aarch64 | 54 | latest | 2024-08-17 14:00:07.553081+00 | 2024-07-30 01:48:22+00 | 2024-07-30 19:00:26.743801+00 | 2024-07-30 19:00:33.899231+00
FreeBSD:14:aarch64 | 26 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-07-29 04:22:32+00 | 2024-07-30 00:04:15.834465+00 | 2024-07-30 00:04:22.69315+00
FreeBSD:14:armv7 | 29 | latest | 2024-08-17 14:00:07.553081+00 | 2024-07-28 02:22:50+00 | 2024-07-28 17:00:37.964482+00 | 2024-07-28 17:01:01.485671+00
FreeBSD:14:aarch64 | 26 | latest | 2024-08-17 14:00:07.553081+00 | 2024-07-25 12:33:52+00 | 2024-07-27 11:00:27.064696+00 | 2024-07-27 11:00:43.953204+00
FreeBSD:13:armv7 | 21 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-07-23 16:57:04+00 | 2024-07-24 02:00:28.636862+00 | 2024-07-24 02:00:33.668385+00
FreeBSD:13:aarch64 | 2 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-07-21 11:07:33+00 | 2024-07-22 03:00:26.416232+00 | 2024-07-22 03:00:33.571772+00
FreeBSD:13:armv7 | 21 | latest | 2024-08-17 14:00:07.553081+00 | 2024-07-19 15:15:06+00 | 2024-07-20 04:00:25.33665+00 | 2024-07-20 04:00:30.30285+00
FreeBSD:13:aarch64 | 2 | latest | 2024-08-17 14:00:07.553081+00 | 2024-07-16 22:32:33+00 | 2024-07-17 19:00:26.851666+00 | 2024-07-17 19:00:33.502219+00
FreeBSD:15:armv7 | 49 | latest | 2024-08-17 14:00:07.553081+00 | 2024-05-03 09:40:13+00 | 2024-05-04 18:00:25.511448+00 | 2024-05-04 18:00:31.552794+00
FreeBSD:15:powerpc | 60 | latest | 2024-08-17 14:00:07.553081+00 | 2024-03-07 14:58:19+00 | 2024-03-08 16:00:25.663001+00 | 2024-03-08 16:09:30.227169+00
FreeBSD:15:powerpc64 | 52 | latest | 2024-08-17 14:00:07.553081+00 | 2024-03-02 07:21:32+00 | 2024-03-06 07:03:37.686013+00 | 2024-03-06 10:21:00.42418+00
FreeBSD:15:powerpc64le | 55 | latest | 2024-08-17 14:00:07.553081+00 | 2024-02-28 12:02:02+00 | 2024-03-06 07:03:43.032949+00 | 2024-03-06 10:23:32.800686+00
FreeBSD:14:powerpc64le | 56 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-02-25 06:21:43+00 | 2024-03-06 07:03:08.479992+00 | 2024-03-06 10:08:38.981137+00
FreeBSD:13:powerpc64le | 57 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-02-24 17:58:34+00 | 2024-03-06 07:01:38.54836+00 | 2024-03-06 08:57:11.484724+00
FreeBSD:14:powerpc | 59 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-02-22 11:32:44+00 | 2024-03-06 07:02:49.056678+00 | 2024-03-06 10:01:53.886761+00
FreeBSD:14:powerpc64 | 33 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-02-19 04:01:38+00 | 2024-03-06 07:02:55.420431+00 | 2024-03-06 10:03:40.503963+00
FreeBSD:13:powerpc | 58 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-02-12 21:14:37+00 | 2024-03-06 07:01:23.250134+00 | 2024-03-06 08:35:07.518979+00
FreeBSD:13:powerpc64 | 24 | quarterly | 2024-08-17 14:00:07.553081+00 | 2024-02-09 12:19:26+00 | 2024-03-06 07:01:32.222869+00 | 2024-03-06 08:47:09.437229+00
FreeBSD:13:armv6 | 20 | quarterly | 2024-08-17 14:00:07.553081+00 | 2023-09-05 12:40:01+00 | 2024-03-06 07:00:54.624081+00 | 2024-03-06 07:55:39.748687+00
FreeBSD:14:powerpc | 59 | latest | 2024-08-17 14:00:07.553081+00 | 2023-07-10 20:07:20+00 | 2024-03-06 07:02:44.26792+00 | 2024-03-06 10:00:03.542631+00
FreeBSD:14:powerpc64le | 56 | latest | 2024-08-17 14:00:07.553081+00 | 2023-06-25 20:36:05+00 | 2024-03-06 07:03:02.948952+00 | 2024-03-06 10:06:14.814574+00
FreeBSD:14:armv6 | 28 | latest | 2024-08-17 14:00:07.553081+00 | 2023-01-15 12:07:08+00 | 2024-03-06 07:02:11.844282+00 | 2024-03-06 09:43:16.056197+00
FreeBSD:13:armv6 | 20 | latest | 2024-08-17 14:00:07.553081+00 | 2021-01-25 12:43:34+00 | 2024-03-06 07:00:50.741456+00 | 2024-03-06 07:50:06.14438+00
FreeBSD:13:powerpc64 | 24 | latest | 2024-08-17 14:00:07.553081+00 | 2021-01-22 06:18:08+00 | 2024-03-06 07:01:28.030171+00 | 2024-03-06 08:42:00.279278+00
FreeBSD:13:powerpc | 58 | latest | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:13:powerpc64le | 57 | latest | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:14:armv6 | 28 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:14:powerpc64 | 33 | latest | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:15:aarch64 | 54 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:15:amd64 | 47 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:15:armv7 | 49 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:15:powerpc | 60 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:15:powerpc64 | 52 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
FreeBSD:15:powerpc64le | 55 | quarterly | 2024-08-17 14:00:07.553081+00 | | |
(44 rows)
freshports.dvl=#
Based on the stored procedure:
CREATE OR REPLACE FUNCTION PackagesLastCheckedSetRepoDate(
a_abi_name text,
a_package_set package_sets,
a_repodate timestamp without time zone)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
l_repo_date timestamp;
BEGIN
SELECT repo_date
INTO l_repo_date
FROM packages_last_checked PLC, abi
WHERE PLC.abi_id = abi.id
AND abi.name = a_abi_name
AND package_set = a_package_set
FOR UPDATE OF PLC;
-- last_checked must always be updated
-- we set the other fields to null when we have a new repo date
-- because we haven't imported that repo and we haven't processed it yet.
-- That assumes import_date and processed_date reflect the repo_date.
UPDATE packages_last_checked PLC
SET last_checked = CURRENT_TIMESTAMP,
repo_date = CASE WHEN repo_date = a_RepoDate THEN repo_date ELSE a_RepoDate END,
import_date = CASE WHEN repo_date = a_RepoDate THEN import_date ELSE null END,
processed_date = CASE WHEN repo_date = a_RepoDate THEN processed_date ELSE null END
FROM abi
WHERE PLC.abi_id = abi.id
AND abi.name = a_abi_name
AND package_set = a_package_set;
-- we compare against the value before the update
RETURN a_RepoDate IS NOT NULL AND l_repo_date IS DISTINCT FROM a_RepoDate;
END;
$BODY$;
My query is:
UPDATE packages_last_checked
SET last_checked = CURRENT_TIMESTAMP,
import_date = null,
processed_date = null
WHERE abi_id = 10
AND package_set = 'quarterly';
Trying it
This looks right:
freshports.dvl=*# UPDATE packages_last_checked
SET last_checked = CURRENT_TIMESTAMP,
import_date = null,
processed_date = null
WHERE abi_id = 10
AND package_set = 'quarterly';
UPDATE 1
freshports.dvl=*# select name, PLC.*
from packages_last_checked PLC join ABI on abi.id = PLC.abi_id
order by repo_date desc nulls last, name, package_set limit 1;
name | abi_id | package_set | last_checked | repo_date | import_date | processed_date
------------------------+--------+-------------+-------------------------------+------------------------+-------------------------------+-------------------------------
FreeBSD:13:i386 | 10 | quarterly | 2024-08-17 14:14:45.527243+00 | 2024-08-16 22:05:01+00 | |
I committed that transaction and set the flags:
[14:15 dvl-ingress01 dvl ~/scripts] % echo touch /var/db/freshports/signals/new_repo_ready_for_import /var/db/freshports/signals/job_waiting | sudo su -fm freshports
The processing began:
Aug 17 14:16:27 dvl-ingress01 FreshPorts[41786]: running job-waiting.pl Aug 17 14:16:27 dvl-ingress01 FreshPorts[41786]: starting ./job-waiting.pl (/usr/local/libexec/freshports) Aug 17 14:16:27 dvl-ingress01 FreshPorts[41786]: running ./job-waiting.pl as user = 'freshports' (/usr/local/libexec/freshports) Aug 17 14:16:27 dvl-ingress01 FreshPorts[41786]: checking jobs for freshports (/usr/local/libexec/freshports) ...
Manual import of file
This is a manual import of the file:
[14:52 dvl-ingress01 dvl ~/scripts] % echo /usr/local/libexec/freshports/import-via-copy-packagesite-all-raw-fields.py -i /var/db/freshports/packagesite/FreeBSD:13:i386/quarterly/packagesite.tsv | sudo su -fm freshports
New strategy for importing repos
signals/new_repo_ready_for_import is the flag
import_packagesite.py is invoked by job-waiting.pl when
import_packagesite.py will invoke:
- fetch-extract-parse-import-one-abi.sh – packages_raw will be populated from packagesite.tsv
- UpdatePackagesFromRawPackages.py – the packages table gets updated, which populates the package_notifications table through triggers
- report-notification-packages.pl which sends emails
Eliminating old flags
job-waiting.pl will also invoke UpdatePackagesFromRawPackages.py if NewRepoImported (new_repo_imported) is set:
/usr/local/etc/freshports/config.ini:SIGNAL_NEW_REPO_IMPORTED = /var/db/freshports/signals/new_repo_imported /usr/local/etc/freshports/config.pm:$FreshPorts::Config::NewRepoImported = $FreshPorts::Config::BaseDir . '/signals/new_repo_imported';
UpdatePackagesFromRawPackages.py will remove SIGNAL_NEW_REPO_IMPORTED
Who sets SIGNAL_NEW_REPO_IMPORTED?
import_packagesite.py: Path(SIGNAL_NEW_REPO_IMPORTED).touch()
However, it doesn’t need to do that any more.
