I’m preparing my test database (freshports.dvl) for code which processes Mk/bsd.default-versions. See the following for references:
Table creation
Today I did this:
[15:18 pg01 dvl ~] % cd ~/src [15:18 pg01 dvl ~/src] % git clone https://github.com/FreshPorts/DefaultVersions Cloning into 'DefaultVersions'... remote: Enumerating objects: 56, done. remote: Counting objects: 100% (56/56), done. remote: Compressing objects: 100% (41/41), done. remote: Total 56 (delta 17), reused 45 (delta 11), pack-reused 0 (from 0) Receiving objects: 100% (56/56), 48.39 KiB | 3.46 MiB/s, done. Resolving deltas: 100% (17/17), done. [15:18 pg01 dvl ~/src] % cd DefaultVersions/PostgreSQL/DDL [15:18 pg01 dvl ~/src/DefaultVersions/PostgreSQL/DDL] % ls -l total 9 -rw-r--r-- 1 dvl dvl 2186 2026.09.11 15:18 0100-default_version_variable.sql -rw-r--r-- 1 dvl dvl 2714 2026.09.11 15:18 0200-port_default_version_variable.sql [15:24 pg01 dvl ~/src/DefaultVersions/PostgreSQL/DDL] % sudo su -l postgres $ cd ~dvl/src/DefaultVersions/PostgreSQL/DDL $ psql freshports.dvl psql (18.6) Type "help" for help. freshports.dvl=# begin; BEGIN freshports.dvl=*# \i 0100-default_version_variable.sql CREATE TABLE COMMENT COMMENT COMMENT COMMENT COMMENT CREATE FUNCTION CREATE TRIGGER freshports.dvl=*# \i 0200-port_default_version_variable.sql CREATE TABLE CREATE INDEX COMMENT COMMENT COMMENT COMMENT COMMENT freshports.dvl=*# commit; COMMIT freshports.dvl=#
Those tables look like this.
default_version_variable
This records all the *_DEFAULT values found in the Mk/bsd.default-versions file.
freshports.dvl=*# \d default_version_variable
Table "public.default_version_variable"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
name | text | | not null |
shape | text | | not null | 'simple'::text
active | boolean | | not null | true
last_synced_at | timestamp with time zone | | not null | now()
created_at | timestamp with time zone | | not null | now()
updated_at | timestamp with time zone | | not null | now()
Indexes:
"default_version_variable_pkey" PRIMARY KEY, btree (id)
"default_version_variable_name_uniq" UNIQUE CONSTRAINT, btree (name)
Check constraints:
"default_version_variable_shape_check" CHECK (shape = ANY (ARRAY['simple'::text, 'conditional'::text, 'complex'::text]))
Referenced by:
TABLE "port_default_version_variable" CONSTRAINT "port_default_version_variable_default_version_variable_id_fkey" FOREIGN KEY (default_version_variable_id) REFERENCES default_version_variable(id) ON DELETE CASCADE
Triggers:
trg_default_version_variable_updated_at BEFORE UPDATE ON default_version_variable FOR EACH ROW EXECUTE FUNCTION default_version_variable_set_updated_at()
If a port uses a given *_DEFAULT value, that port will be recorded here.
freshports.dvl=*# \d port_default_version_variable
Table "public.port_default_version_variable"
Column | Type | Collation | Nullable | Default
-----------------------------+--------------------------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
port_id | integer | | not null |
default_version_variable_id | bigint | | not null |
status | text | | not null |
baseline_portversion | text | | |
baseline_distversion | text | | |
overridden_portversion | text | | |
overridden_distversion | text | | |
probe_value | text | | |
detail | text | | |
checked_at | timestamp with time zone | | not null | now()
Indexes:
"port_default_version_variable_pkey" PRIMARY KEY, btree (id)
"idx_port_default_version_variable_variable_id" btree (default_version_variable_id)
"port_default_version_variable_uniq" UNIQUE CONSTRAINT, btree (port_id, default_version_variable_id)
Check constraints:
"port_default_version_variable_status_check" CHECK (status = ANY (ARRAY['depends'::text, 'depends_error'::text]))
Foreign-key constraints:
"port_default_version_variable_default_version_variable_id_fkey" FOREIGN KEY (default_version_variable_id) REFERENCES default_version_variable(id) ON DELETE CASCADE
"port_default_version_variable_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON DELETE CASCADE
Populating default_version_variable
This is a dry run:
[15:41 dvl-ingress01 dvl ~/scripts] % sudo su -l freshports $ cd /usr/local/libexec/freshports $ python3 sync_default_version_variable.py --repo /jails/freshports/usr/ports --dry-run APACHE_DEFAULT shape=simple active=True BDB_DEFAULT shape=simple active=True COROSYNC_DEFAULT shape=simple active=True EBUR128_DEFAULT shape=conditional active=True EMACS_DEFAULT shape=simple active=False FIREBIRD_DEFAULT shape=conditional active=True FORTRAN_DEFAULT shape=simple active=True FPC_DEFAULT shape=conditional active=True GCC_DEFAULT shape=simple active=True GHOSTSCRIPT_DEFAULT shape=simple active=True GL_DEFAULT shape=simple active=True GO_DEFAULT shape=simple active=True GUILE_DEFAULT shape=simple active=True IMAGEMAGICK_DEFAULT shape=simple active=True JAVA_DEFAULT shape=conditional active=True LAZARUS_DEFAULT shape=conditional active=True LIBRSVG2_DEFAULT shape=conditional active=True LINUX_DEFAULT shape=conditional active=True LLVM_DEFAULT shape=simple active=True LUAJIT_DEFAULT shape=conditional active=True LUA_DEFAULT shape=simple active=True MONO_DEFAULT shape=simple active=True MYSQL_DEFAULT shape=simple active=True NINJA_DEFAULT shape=simple active=True NODEJS_DEFAULT shape=simple active=True OPENLDAP_DEFAULT shape=simple active=True PERL5_DEFAULT shape=complex active=True PGSQL_DEFAULT shape=simple active=True PHP_DEFAULT shape=simple active=True PYCRYPTOGRAPHY_DEFAULT shape=conditional active=True PYTHON2_DEFAULT shape=simple active=True PYTHON_DEFAULT shape=simple active=True RUBY_DEFAULT shape=simple active=True RUST_DEFAULT shape=simple active=True SAMBA_DEFAULT shape=simple active=True SSL_DEFAULT shape=complex active=True SUDO_DEFAULT shape=simple active=True TCLTK_DEFAULT shape=simple active=True VARNISH_DEFAULT shape=simple active=True 39 variables parsed from /jails/freshports/usr/ports/Mk/bsd.default-versions.mk $
Not shown
I did some work not shown here in detail. However, this is an overview.
Create a database user for this.
freshports.dvl=# create role defaults nologin; CREATE ROLE freshports.dvl=# grant select,insert,update,delete on default_version_variable to defaults; GRANT freshports.dvl=# grant select, insert, update, delete on port_default_version_variable to defaults; GRANT freshports.dvl=# create role defaulter_dvl in role defaults login password 'foo'; CREATE ROLE
Allow that user to connect (via ansible file at roles/postgresql-server/templates/databases/freshports.dvl.inc.j2):
hostssl freshports.dvl defaulter_dvl 10.55.0.81/32 {{ postgresql_password_encryption }}
Run that script:
ansible-playbook jail-postgresql.yml –limit=pg01.int.unixathome.org –tags=pg_hba
Run the script
Here, I run sync_default_version_variable.py – code written by Claude. It seems to do the right thing.
$ python3 sync_default_version_variable.py --repo /jails/freshports/usr/ports inserted: 39 ['APACHE_DEFAULT', 'BDB_DEFAULT', 'COROSYNC_DEFAULT', 'EBUR128_DEFAULT', 'EMACS_DEFAULT', 'FIREBIRD_DEFAULT', 'FORTRAN_DEFAULT', 'FPC_DEFAULT', 'GCC_DEFAULT', 'GHOSTSCRIPT_DEFAULT', 'GL_DEFAULT', 'GO_DEFAULT', 'GUILE_DEFAULT', 'IMAGEMAGICK_DEFAULT', 'JAVA_DEFAULT', 'LAZARUS_DEFAULT', 'LIBRSVG2_DEFAULT', 'LINUX_DEFAULT', 'LLVM_DEFAULT', 'LUAJIT_DEFAULT', 'LUA_DEFAULT', 'MONO_DEFAULT', 'MYSQL_DEFAULT', 'NINJA_DEFAULT', 'NODEJS_DEFAULT', 'OPENLDAP_DEFAULT', 'PERL5_DEFAULT', 'PGSQL_DEFAULT', 'PHP_DEFAULT', 'PYCRYPTOGRAPHY_DEFAULT', 'PYTHON2_DEFAULT', 'PYTHON_DEFAULT', 'RUBY_DEFAULT', 'RUST_DEFAULT', 'SAMBA_DEFAULT', 'SSL_DEFAULT', 'SUDO_DEFAULT', 'TCLTK_DEFAULT', 'VARNISH_DEFAULT'] updated: 0 deactivated: 0 [] deleted: 0 [] $
Notice that the first line of output contains both the number of inserts and the list of values inserted.
If I run the code again, it’s very straightforward. And quick:
$ time python3 sync_default_version_variable.py --repo /jails/freshports/usr/ports
inserted: 0 []
updated: 0
deactivated: 0 []
deleted: 0 []
0.17 real 0.13 user 0.01 sys
$
Populating all the ports and *_DEFAULT values
Here’s my first test after getting SSL_DEFAULT coded.
$ time python3 ./populate_port_default_version_deps.py --repo /jails/freshports/usr/ports --dry-run --limit 100 --port net-p2p/litecoin
checking against 38 active *_DEFAULT variables: APACHE_DEFAULT, BDB_DEFAULT, COROSYNC_DEFAULT, EBUR128_DEFAULT, FIREBIRD_DEFAULT, FORTRAN_DEFAULT, FPC_DEFAULT, GCC_DEFAULT, GHOSTSCRIPT_DEFAULT, GL_DEFAULT, GO_DEFAULT, GUILE_DEFAULT, IMAGEMAGICK_DEFAULT, JAVA_DEFAULT, LAZARUS_DEFAULT, LIBRSVG2_DEFAULT, LINUX_DEFAULT, LLVM_DEFAULT, LUAJIT_DEFAULT, LUA_DEFAULT, MONO_DEFAULT, MYSQL_DEFAULT, NINJA_DEFAULT, NODEJS_DEFAULT, OPENLDAP_DEFAULT, PERL5_DEFAULT, PGSQL_DEFAULT, PHP_DEFAULT, PYCRYPTOGRAPHY_DEFAULT, PYTHON2_DEFAULT, PYTHON_DEFAULT, RUBY_DEFAULT, RUST_DEFAULT, SAMBA_DEFAULT, SSL_DEFAULT, SUDO_DEFAULT, TCLTK_DEFAULT, VARNISH_DEFAULT
found 1 active ports to check
checked: 1 ports (0 errored)
depends: 0 ports had at least one *_DEFAULT dependency
upserted: 0 port/variable rows
removed: 0 port/variable rows (now independent)
(--dry-run: no database writes were made)
44.74 real 29.82 user 15.05 sys
$
Next will be setting populating the port_default_version_variable table:
freshports.dvl=# select * from port_default_version_variable; id | port_id | default_version_variable_id | status | baseline_portversion | baseline_distversion | overridden_portversion | overridden_distversion | probe_value | detail | checked_at ----+---------+-----------------------------+---------+----------------------+----------------------+------------------------+------------------------+-------------+--------+------------------------------- 1 | 1959 | 32 | depends | 3.12 | 3.12 | 3.10 | 3.10 | 3.10 | | 2026-09-11 20:32:27.186391+00 (1 row)
The above was populated when I ran:
time python3 ./populate_port_default_version_deps.py –repo /jails/freshports/usr/ports –ports lang/python
Right now, I’m running this command:
freshports 19835 0.2 0.0 46240 30376 1 S+J 20:33 4:03.19 python3 ./populate_port_default_version_deps.py –repo /jails/freshports/usr/ports –category sysutils (python3.14)
It’s been running for just over 3 hours now, populating the 1726 ports of the sysutils category.
That tells me, the whole ports tree may take some time.
