Dan Langille

I've been playing with Open Source software, starting with FreeBSD, since New Zealand Post installed DSL on my street in 1998. From there, I started writing at The FreeBSD Diary, moving my work here after I discovered WordPress. Along the way, I started the BSDCan and PGCon conferences. I slowly moved from software development into full time systems administration and now work for very-well known company who has been a big force in the security industry.

Jun 212021
 

FreshPorts has three main daemons:

  1. ingress – waits for incoming commits and creates XML.
  2. freshports – processes the XML into the database.
  3. fp-listen – listens for events and clears cache

The first two run on the backend (the ingress node, or jail), the last run runs on the webserver (nginx jail).

The list of events

The first thing fp-listen does is read the list of events from the database:

freshports.devgit=# select * from listen_for;
 id |       name        |       script_name        
----+-------------------+--------------------------
  5 | port_updated      | listen_port
  6 | ports_moved       | listen_ports_moved
  7 | ports_updating    | listen_ports_updating
  8 | vuxml             | listen_vuxml
 10 | category_new      | listen_category_new
 11 | date_updated      | listen_date_updated
 12 | packages_imported | listen_packages_imported
 13 | listen_for        | ClearPackagesCache
(8 rows)

Let’s add our new event and the name of the script we-have-yet-to-write:

freshports.devgit=# insert into listen_for (name, script_name) values ('commit_updated', 'listen_commit');
INSERT 0 1

However, note that I won’t actually be created a script by that name.

The Python code

Here is the Python code:

[dan@devgit-nginx01:~/src/fp-listen/fp-listen] $ svn di
Index: fp-listen.py
===================================================================
--- fp-listen.py	(revision 5642)
+++ fp-listen.py	(working copy)
@@ -110,6 +110,44 @@
 
   syslog.syslog(syslog.LOG_NOTICE, "Done with PackagesCacheClear()");
 
+def CommitsCacheClear():
+  dbh = psycopg2.connect(DSN)
+  curs = dbh.cursor(cursor_factory=psycopg2.extras.DictCursor)
+
+  curs.execute("SELECT commit_to_clear FROM cache_clearing_commits")
+  NumRows = curs.rowcount
+  dbh.commit();
+  if (NumRows > 0):
+    syslog.syslog(syslog.LOG_NOTICE, 'COUNT: %d entries to process' % (NumRows))
+    rows = curs.fetchall()
+    for row in rows:
+      #
+      filenameglob = config['dirs']['COMMIT_CACHE_PATH'] % (row['commit_to_clear'])
+      syslog.syslog(syslog.LOG_NOTICE, 'removing glob %s' % (filenameglob))
+
+      try:
+        for filename in glob.glob(filenameglob):
+          syslog.syslog(syslog.LOG_NOTICE, 'removing %s' % (filename))
+          if os.path.isfile(filename):
+            os.remove(filename)
+          else:
+            shutil.rmtree(filename)
+
+      except FileNotFoundError:
+        syslog.syslog(syslog.LOG_CRIT, 'could not find file for deletion %s' % (filename))
+
+      syslog.syslog(syslog.LOG_NOTICE, "DELETE FROM cache_clearing_commits WHERE commit_to_clear = '%s'" % (row['commit_to_clear']))
+      curs.execute("DELETE FROM cache_clearing_commits WHERE commit_to_clear = '%s'" % (row['commit_to_clear']))
+      dbh.commit()
+
+    # end for
+  else:
+    syslog.syslog(syslog.LOG_ERR, 'ERROR: No cached entries found for removal')
+  # end if
+    
+  syslog.syslog(syslog.LOG_NOTICE, 'finished')
+  return NumRows
+
 def ClearDateCacheEntries():
   syslog.syslog(syslog.LOG_NOTICE, 'checking for cache date to remove...')
   dbh = psycopg2.connect(DSN)
@@ -154,6 +192,7 @@
   syslog.syslog(syslog.LOG_NOTICE, 'finished')
   return NumRows
 
+
 def Touch(File):
   if not os.path.exists(File):
     fd = open(File, 'aos.O_WRONLY | os.O_NONBLOCK | os.O_CREAT | os.O_NOCTTY | os.O_APPEND')
@@ -160,6 +199,7 @@
     fd.close()
   os.utime(File, None)
 
+
 def ProcessCategoryNew():
   syslog.syslog(syslog.LOG_NOTICE, 'We have a new category')
   
@@ -167,14 +207,18 @@
   Touch(config['flags']['WWWENPortsCategoriesFlag'])
   Touch(config['flags']['JOBWAITING'])
 
+
 def ProcessPortsMoved():
   syslog.syslog(syslog.LOG_NOTICE, 'processing ports/MOVED')
 
+
 def ProcessPortsUpdating():
   syslog.syslog(syslog.LOG_NOTICE, 'processing ports/UPDATING')
 
+
 def ProcessVUXML():
   syslog.syslog(syslog.LOG_NOTICE, 'processing ports/security/portaudit/vuln.xml')
+
   
 def ClearMiscCaches():
   syslog.syslog(syslog.LOG_NOTICE, 'invoked: ClearMiscCaches()');
@@ -261,6 +305,9 @@
           # at the time of writing, there was no reason to ClearMiscCaches() when
           # new packages arrive
           clear_cache = False;
+        elif listens[notify.channel] == 'listen_commit':
+          syslog.syslog(syslog.LOG_NOTICE, "invoking CommitsClearCache()");
+          CommitsCacheClear()
         else:
           clear_cache = False;
           syslog.syslog(syslog.LOG_ERR, "Code does not know what to do when '%s' is found." % notify.channel)
[dan@devgit-nginx01:~/src/fp-listen/fp-listen] $ 

Directory permissions

When doing the initial testing, I saw this error:

Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]: Traceback (most recent call last):
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:   File "/usr/local/lib/python3.8/site-packages/fp-listen/fp-listen.py", line 310, in 
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:     CommitsCacheClear()
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:   File "/usr/local/lib/python3.8/site-packages/fp-listen/fp-listen.py", line 134, in CommitsCacheClear
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:     shutil.rmtree(filename)
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:   File "/usr/local/lib/python3.8/shutil.py", line 722, in rmtree
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:     onerror(os.rmdir, path, sys.exc_info())
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:   File "/usr/local/lib/python3.8/shutil.py", line 720, in rmtree
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]:     os.rmdir(path)
Jun 21 23:56:17 devgit-nginx01 fp_listen[77170]: PermissionError: [Errno 13] Permission denied: '/var/db/freshports/cache/commits/400c1af36e44f6ad699125526c70668745482c98/'

Ahh, that’s because we have this:

[dan@devgit-nginx01:/usr/local/etc/freshports] $ ls -l /var/db/freshports/cache
total 432
drwxr-xr-x  2 www         freshports       2 Feb 28 18:20 categories
drwxr-xr-x  3 www         freshports       3 Jun 21 23:54 commits
drwxrwxr-x  3 www         freshports       3 Jun 20 15:51 daily
drwxr-xr-x  2 www         freshports       2 Feb 28 18:20 general
drwxrwxr-x  2 freshports  freshports       9 Jun 21 23:00 html
drwxrwxr-x  2 www         freshports       2 Jun 21 23:47 news
drwxrwxr-x  2 www         freshports       2 Feb 28 18:20 packages
drwxr-xr-x  2 www         freshports       2 Feb 28 18:20 pages
drwxrwxr-x  6 www         freshports       6 Jun 20 14:30 ports
-rw-r--r--  1 www         freshports  414331 Jun 13 20:41 searchlog.txt
drwxr-xr-x  2 www         freshports       3 Jun 21 23:54 spooling

Ahh, freshports can’t write to the commits directory, so it cannot delete files. The fp-listen daemon is running as the freshports user:

$ ps auwwx | grep fp-listen
freshports 44828  0.0  0.0  29756 19472  -  SJ   00:06   0:00.19 /usr/local/bin/python /usr/local/lib/python3.8/site-packages/fp-listen/fp-listen.py (python3.8)
dan        77297  0.0  0.0  11456  2776  6  S+J  00:22   0:00.00 grep fp-listen

I can’t just chmod +w here. There is a zfs snapshot involved. Here is what I did instead:

$ sudo zfs rollback nvd/freshports/devgit-nginx01/var/db/freshports/cache/commits@empty
$ sudo zfs destroy nvd/freshports/devgit-nginx01/var/db/freshports/cache/commits@empty
$ sudo chmod g+w commits
$ sudo zfs snapshot nvd/freshports/devgit-nginx01/var/db/freshports/cache/commits@empty

The destroy had to be done on the host itself. This is a situation I’ve seen before where a snapshot cannot be deleted from within the jail, but it can be created.

Configuration file changes

This new entry is required in /usr/local/etc/freshports/fp-listen.ini:

[dan@devgit-nginx01:~/src/fp-listen] $ svn di etc/freshports/fp-listen.ini.sample
Index: etc/freshports/fp-listen.ini.sample
===================================================================
--- etc/freshports/fp-listen.ini.sample	(revision 5642)
+++ etc/freshports/fp-listen.ini.sample	(working copy)
@@ -13,6 +13,7 @@
 BASEDIR         = /var/db/freshports
 
 CATEGORY_CACHE_PATH = %(BASEDIR)s/cache/categories/%%s/*
+COMMIT_CACHE_PATH   = %(BASEDIR)s/cache/commits/%%s/
 PORT_CACHE_PATH     = %(BASEDIR)s/cache/ports/%%s/%%s/*
 DATE_CACHE_PATH     = %(BASEDIR)s/cache/daily/%%s/%%s/%%s.*
 NEWS_CACHE_DIR      = %(BASEDIR)s/cache/news/
[dan@devgit-nginx01:~/src/fp-listen] $ 
Jun 212021
 

Following on from yesterdays Quarterly branches: what’s next? and Processing commits on branches with git, I’ve started in on clearing commits from cache.

The code

First step, create a table which looks like this:

freshports.devgit=# \d cache_clearing_commits
                     Table "public.cache_clearing_commits"
     Column      |            Type             | Collation | Nullable | Default 
-----------------+-----------------------------+-----------+----------+---------
 commit_to_clear | text                        |           | not null | 
 date_added      | timestamp without time zone |           |          | now()
Indexes:
    "cache_clearing_commits_idx" PRIMARY KEY, btree (commit_to_clear)

freshports.devgit=# 

Then, adjust some existing triggers to do something like this:

+   INSERT INTO cache_clearing_commits (commit_to_clear) VALUES (OLD.message_id)
+      ON CONFLICT ON CONSTRAINT cache_clearing_commits_idx DO NOTHING;
+
+   GET DIAGNOSTICS l_row_count = ROW_COUNT;
+   IF l_row_count > 0 THEN
+      NOTIFY commit_updated;
+   END IF;
+

At first, I started created new triggers etc, but then I looked at the existing triggers on the commit_log table:

Triggers:
    commit_log_delete_check BEFORE DELETE ON commit_log FOR EACH ROW EXECUTE FUNCTION commit_log_delete_check()
    commit_log_insert AFTER INSERT ON commit_log FOR EACH ROW EXECUTE FUNCTION commit_log_insert()
    commit_log_update AFTER UPDATE ON commit_log FOR EACH ROW EXECUTE FUNCTION commit_log_update()

I removed what I created and moved that code into the existing triggers.

The test

Let’s do a simple non-destructive test:

freshports.devgit=# begin;
BEGIN
freshports.devgit=# update commit_log set message_id = message_id where message_id = '524260db7683681c7deec9f1968c15a717317685';
UPDATE 1
freshports.devgit=# select * from cache_clearing_commits;
             commit_to_clear              |         date_added         
------------------------------------------+----------------------------
 524260db7683681c7deec9f1968c15a717317685 | 2021-06-21 22:46:35.674694
(1 row)

freshports.devgit=# 

It works.

Next step: modify the fp-listen daemon to listen for commit_updated.

Jun 202021
 

With today’s work on Processing commits on branches with git, good progress was made.

  1. You can see commits on the quarterly branch (not up to date, because it’s not yet automated).
  2. You can see the port on the quarterly branch, if there’s a commit on the branch.
  3. Want to see a particular commit on a quarterly branch? You can.

What’s next?

Automate what I did

I did some coding, then jumped ahead to creation of the XML file.

I have to go back and continue that code. I suspect I’ll need to special case some branches, mainly main.

Clear cache for commits

When I test, I run a commit, delete it, run again. If that commit is cached by the front end, I have to manually delete that. I don’t want to do that any more. I want to add a trigger which raises a NOTIFY event, which the front end will use to clear the cache. See issue 75.

Edit: 2021-06-21 – done. See Commit cache clearing and adding a new event to fp-listen.

See also issue 75.

Fix caching of commits

When you look at a commit on a branch, it says: Commit found by commit id on branch 2021Q2. That is, if you’re the first one to look at it. That gets cached as Commit found by commit id on branch (no branch value shown). This is a known issue.

Edit: 2021-06-22 – done via this commit.

Fix links on quarterly branch commits

See gitea update for an example.

See issue 163

Edit: 2021-06-24 – done via a72b4a9.

Edit: 2021-06-25 – all of the work above (except the automation) is now in production.

Jun 202021
 

Transitioning from subversion to git was both technically and personally challenging. During the COVID-19 pandemic there was a lot of work to carry out. A new server was created on AWS with a new layout and structure. For a few months it was nothing but my day job and FreshPorts in all my non-working hours.

This post was followed by Processing commits on branches with git – part 2.

The new behind-the-scenes code works better than the subversion code, mostly because it no longer relies upon email for notifications of new commits to the code.

One aspect of commit processing not yet tackled is commits on branches.

Back in November 2020, Mathieu Arnold wrote about a proposed approach. This is what I’m going to start on today.

From what I understood, freshports never needs to be able to access
files on the top of a branch, it only needs to access the files on
specific commits (the fact that a commit is at the top of the branch is
only an artefact of the process). So, you never need to checkout any
branches, you only need to checkout commits. So you never have a HEAD
that points to a branch, HEAD is always in a detached state and points
to a commit.

So what you need to do is, git clone the repository, and then, each time
the script runs, do (in somewhat shell script):

NAME_OF_REMOTE=origin
NAME_OF_HEAD=main

cd /where/the/repo/is

git fetch

# get all references (so, branches, tags, and so one) and keep only
# branches (named commits here) that the remote repository has

git for-each-ref --format '%(objecttype) %(refname)' \
  | sed -n 's/^commit refs\/remotes\///p'
  | while read -r type refname
do

  # If we don't have the tag, it means we have not encountered the
  # branch yet.
  if ! git tag -l freshports/$refname
  then

    # get the first commit of that branch and create a tag.
    git tag -m "first known commit of $refname" -f freshports/$refname $(git merge-base $NAME_OF_REMOTE/$NAME_OF_HEAD $refname)
  fi

  # Get the list of commits between the last known one and the tip of
  # the branch, list may be empty.
  git rev-list freshports/$refname..$refname | while read commithash
  do
    # checkout that commit (with -f so that if some file got changed, we
    # overwrite everything
    git checkout -f $commithash

    # process the commit
  done

  # Store the last known commit that we just processed.
  git tag -m "last known commit of $refname" -f freshports/$refname $refname
done

That’s all, you never need to merge or pull or whatever else.

My starting point, the existing git-delta.sh script, which obtains a list of commits which have occurred after a given commit.

Get the repo

Here, I get the ports repo.

mkdir -p ~/src/repos
git clone https://git.FreeBSD.org/ports.git
cd ports

First attempt

[dan@mydev:~/src/repos/ports] $ git for-each-ref --format '%(objecttype) %(refname)' 
commit refs/heads/2021Q2
commit refs/heads/main
commit refs/remotes/origin/2014Q1
commit refs/remotes/origin/2014Q2
commit refs/remotes/origin/2014Q3
commit refs/remotes/origin/2014Q4
commit refs/remotes/origin/2015Q1
commit refs/remotes/origin/2015Q2
commit refs/remotes/origin/2015Q3
commit refs/remotes/origin/2015Q4
commit refs/remotes/origin/2016Q1
commit refs/remotes/origin/2016Q2
commit refs/remotes/origin/2016Q3
commit refs/remotes/origin/2016Q4
commit refs/remotes/origin/2017Q1
commit refs/remotes/origin/2017Q2
commit refs/remotes/origin/2017Q3
commit refs/remotes/origin/2017Q4
commit refs/remotes/origin/2018Q1
commit refs/remotes/origin/2018Q2
commit refs/remotes/origin/2018Q3
commit refs/remotes/origin/2018Q4
commit refs/remotes/origin/2019Q1
commit refs/remotes/origin/2019Q2
commit refs/remotes/origin/2019Q3
commit refs/remotes/origin/2019Q4
commit refs/remotes/origin/2020Q1
commit refs/remotes/origin/2020Q2
commit refs/remotes/origin/2020Q3
commit refs/remotes/origin/2020Q4
commit refs/remotes/origin/2021Q1
commit refs/remotes/origin/2021Q2
commit refs/remotes/origin/HEAD
commit refs/remotes/origin/main
tag refs/tags/10-eol
tag refs/tags/4-eol
tag refs/tags/5-eol
tag refs/tags/6-eol
tag refs/tags/7-eol
tag refs/tags/8-eol
tag refs/tags/9-eol
tag refs/tags/pkg-install-eol
tag refs/tags/pre-xorg-7
tag refs/tags/release/10.0.0
tag refs/tags/release/10.1.0
tag refs/tags/release/10.2.0
tag refs/tags/release/10.3.0
tag refs/tags/release/10.4.0
tag refs/tags/release/11.0.0
tag refs/tags/release/11.1.0
tag refs/tags/release/11.2.0
tag refs/tags/release/11.3.0
tag refs/tags/release/11.4.0
tag refs/tags/release/12.0.0
tag refs/tags/release/12.1.0
tag refs/tags/release/12.2.0
tag refs/tags/release/13.0.0
tag refs/tags/release/2.0.5
tag refs/tags/release/2.0.5a
tag refs/tags/release/2.1.0
tag refs/tags/release/2.1.5
tag refs/tags/release/2.1.6
tag refs/tags/release/2.1.7
tag refs/tags/release/2.2.0
tag refs/tags/release/2.2.1
tag refs/tags/release/2.2.2
tag refs/tags/release/2.2.5
tag refs/tags/release/2.2.6
tag refs/tags/release/2.2.7
tag refs/tags/release/2.2.8
tag refs/tags/release/3.0.0
tag refs/tags/release/3.1.0
tag refs/tags/release/3.2.0
tag refs/tags/release/3.3.0
tag refs/tags/release/3.4.0
tag refs/tags/release/3.5.0
tag refs/tags/release/4.0.0
tag refs/tags/release/4.1.0
tag refs/tags/release/4.1.1
tag refs/tags/release/4.10.0
tag refs/tags/release/4.11.0
tag refs/tags/release/4.2.0
tag refs/tags/release/4.3.0
tag refs/tags/release/4.4.0
tag refs/tags/release/4.5.0
tag refs/tags/release/4.6.0
tag refs/tags/release/4.6.1
tag refs/tags/release/4.6.2
tag refs/tags/release/4.7.0
tag refs/tags/release/4.8.0
tag refs/tags/release/4.9.0
tag refs/tags/release/5.0.0
tag refs/tags/release/5.1.0
tag refs/tags/release/5.2.0
tag refs/tags/release/5.2.1
tag refs/tags/release/5.3.0
tag refs/tags/release/5.4.0
tag refs/tags/release/5.5.0
tag refs/tags/release/6.0.0
tag refs/tags/release/6.1.0
tag refs/tags/release/6.2.0
tag refs/tags/release/6.3.0
tag refs/tags/release/6.4.0
tag refs/tags/release/7.0.0
tag refs/tags/release/7.1.0
tag refs/tags/release/7.2.0
tag refs/tags/release/7.3.0
tag refs/tags/release/7.4.0
tag refs/tags/release/8.0.0
tag refs/tags/release/8.1.0
tag refs/tags/release/8.2.0
tag refs/tags/release/8.3.0
tag refs/tags/release/8.4.0
tag refs/tags/release/9.0.0
tag refs/tags/release/9.1.0
tag refs/tags/release/9.2.0
tag refs/tags/release/9.3.0
[dan@mydev:~/src/repos/ports] $ 
Raw

Applying the filter

My first try brought back empty strings:

[dan@mydev:~/src/repos/ports] $ git for-each-ref --format '%(objecttype) %(refname)' \
  | sed -n 's/^commit refs\/remotes\///p' \
  | while read -r type refname
do
  echo ref is "'$refname'"
done
ref is ''
ref is ''
ref is ''
ref is ''
...

madree explained this to me as: “the sed expression comes up with just freebsd/HEAD and freebsd/main for me and then read can’t possibly read two columns (type and refname). try omitting the “type” word and use just “while read -r refname” and see what you get”.

And so I did:

[dan@mydev:~/src/repos/ports] $ git for-each-ref --format '%(objecttype) %(refname)'   | sed -n 's/^commit refs\/remotes\///p'   | while read -r refname; do   echo ref is "'$refname'"; done
ref is 'origin/2014Q1'
ref is 'origin/2014Q2'
ref is 'origin/2014Q3'
ref is 'origin/2014Q4'
ref is 'origin/2015Q1'
ref is 'origin/2015Q2'
ref is 'origin/2015Q3'
ref is 'origin/2015Q4'
ref is 'origin/2016Q1'
ref is 'origin/2016Q2'
ref is 'origin/2016Q3'
ref is 'origin/2016Q4'
ref is 'origin/2017Q1'
ref is 'origin/2017Q2'
ref is 'origin/2017Q3'
ref is 'origin/2017Q4'
ref is 'origin/2018Q1'
ref is 'origin/2018Q2'
ref is 'origin/2018Q3'
ref is 'origin/2018Q4'
ref is 'origin/2019Q1'
ref is 'origin/2019Q2'
ref is 'origin/2019Q3'
ref is 'origin/2019Q4'
ref is 'origin/2020Q1'
ref is 'origin/2020Q2'
ref is 'origin/2020Q3'
ref is 'origin/2020Q4'
ref is 'origin/2021Q1'
ref is 'origin/2021Q2'
ref is 'origin/HEAD'
ref is 'origin/main'
[dan@mydev:~/src/repos/ports] $ 

This. This is more like it.

Getting the starting point

First, some background on the pseudo code.

At present, FreshPorts stores the ‘starting’ point, also known as the last commit, in a file: latest.ports.

Mathieu’s approach uses a tag in the repo itself. I was hesitant at first. If we lose the repo, we lose our starting points.

Line 18 from above has: if ! git tag -l freshports/$refname

That’s looking for the git tag. If we don’t find it, we know we have not processed this branch before. The typical use case here is a new quarterly branch has arrived and we want to process it. The first step of that processing is identifying the first commit on the branch.

Line 22 contains: git merge-base $NAME_OF_REMOTE/$NAME_OF_HEAD $refname)

NAME_OF_REMOTE and NAME_OF_HEAD confused me for a while. madree to the rescue.

[dan@mydev:~/src/repos/ports] $ git remote -v
origin	https://git.FreeBSD.org/ports.git (fetch)
origin	https://git.FreeBSD.org/ports.git (push)

[dan@mydev:~/src/repos/ports] $ git remote show
origin

There. NAME_OF_REMOTE is origin.

NAME_OF_HEAD is main. I can see that here:

[dan@mydev:~/src/repos/ports] $ git branch
* main
[dan@mydev:~/src/repos/ports] $ 

For this example, let’s use refname origin/2021Q2:

[dan@mydev:~/src/repos/ports] $ git merge-base origin/main origin/2021Q2
4e3cf0163c4a00d4dac41d6da43472d2fcab2f29

Looking at that commit:

author	Yuri Victorovich <yuri@FreeBSD.org>	2021-04-06 21:01:18 +0000
www/yt-dlp: Update 2021.03.24.1 -> 2021.04.03
PR:		254782
Submitted by:	daniel.engberg.lists@pyret.net

Is that really the first commit on the 2021Q2 branch?

I think it is not. But let’s continue with this exercise. Let’s see the first four commits.

[dan@mydev:~/src/repos/ports] $ git rev-list 4e3cf0163c4a00d4dac41d6da43472d2fcab2f29..origin/2021Q2 | head -4
5ceea227c504d2892d91c1aa8d8d81ff15b22fc3
3ce47d16f7eb5c00b470603c307fa52bb9ca920b
700466498a3c9c550882b91c9e9efec2ac533346
ce9f001bf8e1d5ef297e1a869cfb97f75f750c71
[dan@mydev:~/src/repos/ports] $ 

It is difficult for me to confirm whether or a commit is on a given branch. It’s not trivial.

Let’s try this for now, using the code from git_proc_commit

$ cd ~/src/freshports/git_proc_commit/git-to-freshports
$ ./git-to-freshports-xml.py --repo ports --path ~/src/repos/ports \
--commit-range 4e3cf0163c4a00d4dac41d6da43472d2fcab2f29..origin/2021Q2 \
--spooling ~/src/msgs/tmp --output ~/src/msgs/xml
$ ls ~/src/msgs/xml/ | wc -l
     590
$ grep -l 'Branch="2021Q2"' * | wc -l
     590

Those 590 files are now in the above repo as 2021Q2 examples. Everyone one of them contains Branch=”2021Q2″.

Future work

Future works means deleting those commits after fixing this issue:

Observer has noticed that commit 'eca26f0cd09d10ab7becf712579a1470768d966c' contains file /ports/2021Q2/sysutils/cbsd/distinfo as revision eca26f0cd09d10ab7becf712579a1470768d966c in repository ports
STARTING _CompileListOfPorts ................................
for a commit on 'branch': '2021Q2'
this commit is NOT ON head
FILE ==: Modify, /ports/2021Q2/sysutils/cbsd/Makefile, eca26f0cd09d10ab7becf712579a1470768d966c, ports, cbsd, Makefile, 4431078
YES, this file is in the ports tree
... but this file is not part of a physical category on disk!

Problem is, that is a valid physical category. On a branch. Adjustments are required.

In addition, that’s the wrong path. It should be /ports/branches/2021Q2 not /ports/2021Q2.

Fixed

The path thing is fixed. It was an issue on incoming commits, now repaired with:

 	my $NewRevision		= 0;
 	my $element;
 	my $element_id;
+	my $filename;
 	# This is where we add in the repo name to the path
-	my $filename     = $DB_Root_Prefix . '/' . $Updates{branch_for_files} . '/' . $FilePath;
+	# At one time, I think, $Updates{branch_for_files} was to be either head or branches/2021Q2 (or example).
+	# As of 2021.06.20, it is either head or 2021Q2 (no branches).
+	# I think the best thing to do is to check $Updates{branch_for_files} here and add in braches when required.
+	if ($Updates{branch_for_files} eq $FreshPorts::Constants::HEAD) {
+		$filename = $DB_Root_Prefix . '/' .          $Updates{branch_for_files} . '/' . $FilePath;
+	} else {
+		$filename = $DB_Root_Prefix . '/branches/' . $Updates{branch_for_files} . '/' . $FilePath;
+	}	

The 560 commits from 2021Q2 are now being imported.

This blog post has been updated throughout the day. It is now finished. Next, I’ll write up what’s next.

May 312021
 

Today I discovered a problem which required the reprocessing of about 400 commits.

How is that accomplished?

  1. delete the commits from the database
  2. tell the ingress daemon to start processing from commit x

Deleting

This works so long as all the commits have been process in order. The canonical method would query git to get a list of commits from A to B and then delete only those commits.

If you’ve been messing about with other commits and rerunning them, the method you see below may delete more than you anticipate.

The last successful commit was d9058fd29e8c3521cdece20a56a0ffdde4c1647f. This SQL deletes all commits which came in after that commit. This takes advantage of increasing values in the id field.

freshports.testgit=# begin; delete from commit_log where id > (select id from commit_log where svn_revision = 'd9058fd29e8c3521cdece20a56a0ffdde4c1647f');
BEGIN


DELETE 394
freshports.testgit=# 
freshports.testgit=# 
freshports.testgit=# commit;
COMMIT
freshports.testgit=# 

Reprocessing

This step involves resetting the starting point for commit processing.

[dan@testgit-ingress01:/var/db/ingress/repos] $ cat latest.ports 
3aa7a82534ec67d837a2e305480b45007f3d9b15
[dan@testgit-ingress01:/var/db/ingress/repos] $ echo 'echo d9058fd29e8c3521cdece20a56a0ffdde4c1647f > latest.ports' | sudo su -fm ingress
[dan@testgit-ingress01:/var/db/ingress/repos] cat latest.ports 
d9058fd29e8c3521cdece20a56a0ffdde4c1647f
[dan@testgit-ingress01:/var/db/ingress/repos] $ 

EDIT:

What the delete fails to recognize is it will delete all commits, src, doc, and ports.

Therefore, you have to rerun commits for src and doc, not just ports.

[dan@testgit-ingress01:/var/db/ingress/repos] echo 'echo 1f76329528e0a19a002ca0c174819bc4290fe04d > latest.doc' | sudo su -fm ingress
[dan@testgit-ingress01:/var/db/ingress/repos] echo 'echo 5e912f5fec025766521f535d1237330ede7f18e2 > latest.src' | sudo su -fm ingress
May 292021
 

Today at about 1:00 pm Philadelphia time, the website went offline. It was still there, it just could not answer any queries.

In this post:

  • Website and database server running in AWS
  • FreeBSD 12.2 on a
  • t3.medium EC2 instance
  • PostgreSQL 12.5 running on a db.t2.large RDS instance

Initial look

Looking at load, it was acceptable:

$ w
 7:09PM  up 43 days, 20:48, 1 user, load averages: 0.12, 0.26, 0.30
USER       TTY      FROM                                      LOGIN@  IDLE WHAT
ec2-user   pts/0    [redacted]                                7:09PM     - w

There were a lot of php-fpm: pool www (php-fpm) processes running.

Looking in the logs, I found:

[root@nginx01 /var/log]# tail -F php-fpm.log 
[29-May-2021 17:41:34] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 16 children, there are 17 idle, and 224 total children
[29-May-2021 17:41:41] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 19 idle, and 234 total children
[29-May-2021 17:41:43] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 16 children, there are 17 idle, and 235 total children
[29-May-2021 17:41:44] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 18 idle, and 238 total children
[29-May-2021 17:41:45] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 18 idle, and 240 total children
[29-May-2021 17:41:46] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 19 idle, and 242 total children
[29-May-2021 17:41:51] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 18 idle, and 249 total children
[29-May-2021 17:41:55] WARNING: [pool www] server reached pm.max_children setting (250), consider raising it
[29-May-2021 18:02:26] WARNING: [pool www] child 12962 exited with code 70 after 1759.560106 seconds from start
[29-May-2021 18:02:26] NOTICE: [pool www] child 22023 started
^C

Yeah, that’s a lot.

Looking at the RDS statistics, it was busy at about 98% load I think, I did not take a screen shot then. But here is the screen shot in hindsight, showing the fast build up of the number of sessions, from near zero to 250 and staying there for 2.5 hours

250 database sessions

250 database sessions

From this:

  • select * from elementGetCaseInsensitive() is suspect
  • this happened suddenly

The query

Here is the query:

   SELECT tmp.*, EP.pathname FROM (
      SELECT id,
             name::text,
             directory_file_flag::text,
             status::text,
             case when IsCategory(id) IS NULL THEN FALSE ELSE TRUE END,
             case when IsPort(    id) IS NULL THEN FALSE ELSE TRUE END
        FROM element
       WHERE id = (SELECT element_id
        FROM element_pathname
       WHERE lower(pathname) = lower($1))
     ) AS tmp JOIN element_pathname EP on tmp.id = EP.element_id;

And an explain analyse:

freshports.org=> explain analyse select * from elementGetCaseInsensitive('/ports/head/sysutils/nut');
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=29669.04..29685.59 rows=1 width=137) (actual time=6396.216..6396.221 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on element_pathname  (cost=0.00..29668.18 rows=5748 width=4) (actual time=4123.990..6395.702 rows=1 loops=1)
           Filter: (lower(pathname) = '/ports/head/sysutils/nut'::text)
           Rows Removed by Filter: 1149267
   ->  Index Scan using element_pkey on element  (cost=0.43..8.45 rows=1 width=23) (actual time=6395.745..6395.746 rows=1 loops=1)
         Index Cond: (id = $0)
   ->  Index Scan using element_pathname_element_id on element_pathname ep  (cost=0.43..8.45 rows=1 width=56) (actual time=0.008..0.009 rows=1 loops=1)
         Index Cond: (element_id = $0)
 Planning Time: 0.392 ms
 Execution Time: 6396.252 ms
(11 rows)

That’s terrible. Look at that sequential scan on over 1.1 million rows.

I know how to improve this: a new index.

The alteration

This is how I altered the table, first, the original definition:

freshports.org-> \d element_pathname
            Table "public.element_pathname"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 element_id | integer |           | not null | 
 pathname   | text    |           | not null | 
Indexes:
    "element_pathname_pathname" UNIQUE, btree (pathname)
    "element_pathname_element_id" btree (element_id)
Foreign-key constraints:
    "element_pathname_element_id_fkey" FOREIGN KEY (element_id) REFERENCES element(id) ON DELETE CASCADE

freshports.org-> 

This is the index I added:

freshports.org=> begin;
BEGIN
freshports.org=> create index element_pathname_pathname_lc on element_pathname (lower(pathname));
CREATE INDEX
freshports.org=> commit;

The new explain analyze

Now that query runs quickly:

freshports.org=> explain analyse select * from elementGetCaseInsensitive('/ports/head/sysutils/nut');
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=10438.60..10455.15 rows=1 width=137) (actual time=0.292..0.297 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Bitmap Heap Scan on element_pathname  (cost=265.08..10437.74 rows=5746 width=4) (actual time=0.042..0.043 rows=1 loops=1)
           Recheck Cond: (lower(pathname) = '/ports/head/sysutils/nut'::text)
           Heap Blocks: exact=1
           ->  Bitmap Index Scan on element_pathname_pathname_lc  (cost=0.00..263.65 rows=5746 width=0) (actual time=0.037..0.038 rows=1 loops=1)
                 Index Cond: (lower(pathname) = '/ports/head/sysutils/nut'::text)
   ->  Index Scan using element_pkey on element  (cost=0.43..8.45 rows=1 width=23) (actual time=0.053..0.054 rows=1 loops=1)
         Index Cond: (id = $0)
   ->  Index Scan using element_pathname_element_id on element_pathname ep  (cost=0.43..8.45 rows=1 width=56) (actual time=0.007..0.008 rows=1 loops=1)
         Index Cond: (element_id = $0)
 Planning Time: 0.604 ms
 Execution Time: 0.332 ms
(13 rows)

freshports.org=> 

With that change, the website soon returned to its usually snappiness.

The stats show the results

This screen shows how the number of session is back to normal. see also that the query in question is now listed down under 0.01 seconds. Good.

sessions down near 0

sessions down near 0

I should have known to add this index with that new function was created. I don’t see an explain analyze in that post.

May 092021
 

This post is a how-to and reminder for myself. I’m working on finding a better way for FreshPorts to know a new physical ports category when it finds it. I think the only way is to look at the directories in the FreeBSD ports repo.

A discussion on IRC led to this shell script:

[dan@devgit-ingress01:/var/db/ingress/repos/ports] $ find -s -f * -type d -regex '[a-z].*' -maxdepth 0 | xargs | wc -w
      62

I will break that down for future reference:

  1. -s : traverse the file hierarchies in lexicographical; it gives us the output in sorted order
  2. -f * : avoids nasty situations where there might be a file named -foo in the tree. It also avoids use of find . which would give results with a leading ./ in the filenames
  3. -type d – only directories
  4. -regex ‘[a-z].*’ – match only items which start with a lower case character (i.e. ignore Mk, LEGAL, COPYRIGHT, .hooks
  5. -maxdepth 0 – stay in the top level directory
  6. | xargs – pipe the results onto one line
  7. | wc -w – count the number of words returned; included here only for testing purposes

The need for this script arose when FreshPorts encountered a commit for a .dotfile. While this wasn’t the first commit of such a file, it did bring my attention to the need to find a list of categories. The commit in question added the .hooks/prepare-commit-msg file to the ports tree. The code incorrectly concluded this was a new port (i.e. prepare-commit-msg). FreshPorts kept looking for a Makefile for this port, and never found it. It kept looking, by waiting, and trying again. The code could have seen, by looking in the repo, that this was a file, not a directory, and no Makefile was coming.

Instead, I’m taking a different approach. With each commit, the code will query the ports repo and get a list of the current categories. This list will be referenced and the code will know whether a given file affects a port or not. As I type this, that code is not yet written, but it will get written as I go through this process.

Part of the debug log

FreshPorts logs. A lot. The commit in question has 1307 lines of output:

[dan@devgit-ingress01:/var/db/freshports/message-queues/retry] $ wc -l 2021.04.20.09.58.35.000000.bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248.log
    1307 2021.04.20.09.58.35.000000.bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248.log

The file name is taken from the date/time that the file commit was processed by FreshPorts, in this case, 2021-04-20 at 09:58:35 (all times are UTC). The 000000 is a counter, so this was the first commit found during that processing time. We have room for 1,000,000 commits at a time before that counter rolls over. Next is the commit hash, which should be unique enough, but we still prefix the file name with that timestamp.

On line 115 of that log file, we find:

getting id from 'commit_log_elements_id_seq'
sql is insert into commit_log_elements(id, commit_log_id, element_id, revision_name, change_type) values 
                                        (4388001, 849545, 1215757, 'bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248', 'A')
sql = 'select ElementTagSet(1, 1215757, 'bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248')'
pushing the following onto @Files
FileAction='Add'
FilePath='/ports/head/.hooks/prepare-commit-msg'
FileRevision='bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248'
commit_log_element->{id}='4388001'
element_id='1215757'
Observer has noticed that commit 'bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248' contains file /ports/head/.hooks/prepare-commit-msg as revision bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248 in repos
STARTING _CompileListOfPorts ................................
for a commit on 'branch': 'head'
this commit is on head
FILE ==: Add, /ports/head/.hooks/prepare-commit-msg, bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248, ports, .hooks, prepare-commit-msg, 4388001
YES, this file is in the ports tree
checking for category='.hooks'
sql = "select * from categories where name = '.hooks'"
NOT FOUND
creating new category .hooks

Line 130 correctly determines that this file is in the ports tree.

Line 131 is where things start going wrong. This is not a category.

Ignored items

Way back when, a list of ignored items was created:

#
# These are the entries within /usr/ports/ which we ignore
# and /usr/ports/<category> which FreshPorts does not track
#
%FreshPorts::Constants::IgnoredItems = (
        "Attic"        => 1,
        "distfiles"    => 2,
        "Mk"           => 3,
        "Tools"        => 4,
        "Templates"    => 5,
        "Makefile"     => 6,
        "Makefile.inc" => 7,
        "CVSROOT"      => 8,
        "base"         => 9,
);

This was good enough for the early days. The list of IgnoredItems dates back to Fri Nov 9 16:30:29 2001 when it was a single string:

$FreshPorts::Constants::IgnoredItems = "Attic|distfiles|Mk|Tools|Templates|Makefile|pkg";

Today, I’m removing that and introducing a new module: FreshPorts::Catgories.

Getting the XML

Getting back to the original purpose of this post, creating XML.

Based on git commit processing – how is it done?, I found a reference to git-to-freshports-xml.py.

I tried this:

 $ ./git-to-freshports-xml.py
usage: git-to-freshports-xml.py [-h] -p PATH -O OUTPUT -S SPOOLING -r REPO [-o OS] [-f] [-v] [-l {syslog,stderr}] (-c COMMIT | -s SINGLE_COMMIT | -R COMMIT_RANGE)
git-to-freshports-xml.py: error: the following arguments are required: -p/--path, -O/--output, -S/--spooling, -r/--repo

Looking in the logs, I found this:

[dan@devgit-ingress01:~/scripts] $ sudo grep git-to-freshports-xml.py /var/log/freshports/git.log | head -1
2021.05.09 00:03:14 git-delta.sh /usr/local/libexec/freshports/git-to-freshports-xml.py --repo doc --path /var/db/ingress/repos/doc --commit cf1bad339628407060c88a1b20218f0c9660ba11 --spooling /var/db/ingress/message-queues/spooling --output /var/db/ingress/message-queues/incoming

Now I have something I can use to create the XML.

But if you run that, like I did, you’ll get thousands of XML files. I need to use -s instead of –commit.

This worked:

[dan@devgit-ingress01:~/scripts] $ /usr/local/libexec/freshports/git-to-freshports-xml.py --repo ports --path /var/db/ingress/repos/ports -s  bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248 --spooling /tmp/ --output ~/tmp

And created:

[dan@devgit-ingress01:~/tmp] $ cat 2021.04.20.09.58.35.000000.bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248.xml 
<?xml version='1.0' encoding='UTF-8'?>
<UPDATES Version="1.5.0.0" Source="git">
  <UPDATE>
    <DATE Year="2021" Month="4" Day="20"/>
    <TIME Timezone="UTC" Hour="9" Minute="58" Second="35"/>
    <OS Repo="ports" Id="FreeBSD" Branch="main"/>
    <LOG>Add the prepare-commit-msg hook to the repository.

To make use of it, the easiest way is to run:

  git config --add core.hooksPath .hooks

Discussed with:	bapt</LOG>
    <PEOPLE>
      <COMMITTER CommitterName="Mathieu Arnold" CommitterEmail="mat@FreeBSD.org"/>
      <AUTHOR AuthorName="Mathieu Arnold" AuthorEmail="mat@FreeBSD.org"/>
    </PEOPLE>
    <COMMIT Hash="bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248" HashShort="bbc2474" Subject="Add the prepare-commit-msg hook to the repository." EncodingLoses="false" Repository="ports"/>
    <FILES>
      <FILE Action="Add" Path=".hooks/prepare-commit-msg"/>
    </FILES>
  </UPDATE>
</UPDATES>
[dan@devgit-ingress01:~/tmp] $

The code change

The code change looks like this:

-                       # look for special files outside a port, such as LEGAL, GIDs, UIDs
-                       if ($subtree eq $FreshPorts::Config::ports_prefix && defined($FreshPorts::Constants::IgnoredItems{$category_name})) {
+                       # look for special files outside a port, such as LEGAL, GIDs, UIDs, .hooks
+                       if ( ! any {/$category_name/} @FreshPorts::Categories::categories ) {

That any function comes in via use List::MoreUtils ‘any’; and is part of lang/p5-List-MoreUtils, which is already a dependency on all FreshPorts hosts.

Reprocessing the errant commit

When I reprocessed that .hooks commit, I got this:

getting id from 'commit_log_elements_id_seq'
sql is insert into commit_log_elements(id, commit_log_id, element_id, revision_name, change_type) values 
                                        (4397803, 852396, 1217151, 'bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248', 'A')
sql = 'select ElementTagSet(1, 1217151, 'bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248')'
pushing the following onto @Files
FileAction='Add'
FilePath='/ports/head/.hooks/prepare-commit-msg'
FileRevision='bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248'
commit_log_element->{id}='4397803'
element_id='1217151'
Observer has noticed that commit 'bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248' contains file /ports/head/.hooks/prepare-commit-msg as revision bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248 in repos
STARTING _CompileListOfPorts ................................
for a commit on 'branch': 'head'
this commit is on head
FILE ==: Add, /ports/head/.hooks/prepare-commit-msg, bbc2474ef7a65eb8561c8ecf7af80c2bfed1f248, ports, .hooks, prepare-commit-msg, 4397803
YES, this file is in the ports tree
... but is not a file in a category on disk!

ENDING _CompileListOfPorts ................................

The code correctly determines that this is not a port, because .hooks is not in the list of categories.

Win. Thank you for coming to my TED talk.

May 082021
 

This post is related to a recent tweet.

It was months ago that I wondered why one port installed another as a dependency. I could see no link. Since it FreshPorts ports, earlier this week I did some testing: I removed the package, and it’s own dependencies from dev, then test, and finally stage.

Monitoring checks would soon detect if the #FreeBSD commits on each website started to deviate from the others, including production, which was not touched for this test.

By the end of the week, and after reviewing the code, it was safe to remove the dependency.

Today, that change hit production.

I like that the package system keeps track of dependencies. When that one package was removed, many others could be removed. i.e. its dependencies, & their dependencies, etc.

All up, 114 package were removed from the FreshPorts webserver.

pkg autoremove is a wonderful tool.

This was the pkg update:

[root@nginx01 /]# pkg upgrade
Updating local repository catalogue...
[nginx01] Fetching meta.conf: 100%    163 B   0.2kB/s    00:01    
[nginx01] Fetching packagesite.txz: 100%  267 KiB 273.0kB/s    00:01    
Processing entries: 100%
local repository update completed. 1127 packages processed.
All repositories are up to date.
Checking for upgrades (30 candidates): 100%
Processing candidates (30 candidates): 100%
The following 29 package(s) will be affected (of 0 checked):

Installed packages to be UPGRADED:
	bash: 5.1.4_2 -> 5.1.8
	freshports-www-git: 2.0.20 -> 2.0.22_1
	p5-CGI: 4.51 -> 4.52
	php74: 7.4.18 -> 7.4.19
	php74-ctype: 7.4.18 -> 7.4.19
	php74-dom: 7.4.18 -> 7.4.19
	php74-exif: 7.4.18 -> 7.4.19
	php74-fileinfo: 7.4.18 -> 7.4.19
	php74-filter: 7.4.18 -> 7.4.19
	php74-gd: 7.4.18 -> 7.4.19
	php74-iconv: 7.4.18 -> 7.4.19
	php74-json: 7.4.18 -> 7.4.19
	php74-mbstring: 7.4.18 -> 7.4.19
	php74-opcache: 7.4.18 -> 7.4.19
	php74-pgsql: 7.4.18 -> 7.4.19
	php74-phar: 7.4.18 -> 7.4.19
	php74-posix: 7.4.18 -> 7.4.19
	php74-session: 7.4.18 -> 7.4.19
	php74-simplexml: 7.4.18 -> 7.4.19
	php74-tokenizer: 7.4.18 -> 7.4.19
	php74-xml: 7.4.18 -> 7.4.19
	php74-xmlreader: 7.4.18 -> 7.4.19
	php74-xmlwriter: 7.4.18 -> 7.4.19
	php74-zip: 7.4.18 -> 7.4.19
	php74-zlib: 7.4.18 -> 7.4.19
	postfix: 3.5.10,1 -> 3.6.0,1
	py38-freshports-fp-listen-git: 1.0.2_4 -> 1.0.2_5
	py38-six: 1.15.0 -> 1.16.0
	sqlite3: 3.35.5,1 -> 3.35.5_1,1

Number of packages to be upgraded: 29

12 MiB to be downloaded.

Proceed with this action? [y/N]: y
[nginx01] [1/29] Fetching sqlite3-3.35.5_1,1.txz: 100%    1 MiB   1.3MB/s    00:01    
[nginx01] [2/29] Fetching py38-six-1.16.0.txz: 100%   19 KiB  19.5kB/s    00:01    
[nginx01] [3/29] Fetching py38-freshports-fp-listen-git-1.0.2_5.txz: 100%    8 KiB   7.8kB/s    00:01    
[nginx01] [4/29] Fetching postfix-3.6.0,1.txz: 100%    2 MiB   1.6MB/s    00:01    
[nginx01] [5/29] Fetching php74-zlib-7.4.19.txz: 100%   16 KiB  16.5kB/s    00:01    
[nginx01] [6/29] Fetching php74-zip-7.4.19.txz: 100%   19 KiB  19.6kB/s    00:01    
[nginx01] [7/29] Fetching php74-xmlwriter-7.4.19.txz: 100%   11 KiB  11.3kB/s    00:01    
[nginx01] [8/29] Fetching php74-xmlreader-7.4.19.txz: 100%   11 KiB  10.8kB/s    00:01    
[nginx01] [9/29] Fetching php74-xml-7.4.19.txz: 100%   18 KiB  18.1kB/s    00:01    
[nginx01] [10/29] Fetching php74-tokenizer-7.4.19.txz: 100%    7 KiB   7.7kB/s    00:01    
[nginx01] [11/29] Fetching php74-simplexml-7.4.19.txz: 100%   21 KiB  21.4kB/s    00:01    
[nginx01] [12/29] Fetching php74-session-7.4.19.txz: 100%   34 KiB  34.3kB/s    00:01    
[nginx01] [13/29] Fetching php74-posix-7.4.19.txz: 100%   11 KiB  10.8kB/s    00:01    
[nginx01] [14/29] Fetching php74-phar-7.4.19.txz: 100%  102 KiB 104.5kB/s    00:01    
[nginx01] [15/29] Fetching php74-pgsql-7.4.19.txz: 100%   40 KiB  41.3kB/s    00:01    
[nginx01] [16/29] Fetching php74-opcache-7.4.19.txz: 100%  198 KiB 202.8kB/s    00:01    
[nginx01] [17/29] Fetching php74-mbstring-7.4.19.txz: 100%  743 KiB 761.1kB/s    00:01    
[nginx01] [18/29] Fetching php74-json-7.4.19.txz: 100%   20 KiB  20.0kB/s    00:01    
[nginx01] [19/29] Fetching php74-iconv-7.4.19.txz: 100%   16 KiB  16.2kB/s    00:01    
[nginx01] [20/29] Fetching php74-gd-7.4.19.txz: 100%   29 KiB  29.4kB/s    00:01    
[nginx01] [21/29] Fetching php74-filter-7.4.19.txz: 100%   18 KiB  18.3kB/s    00:01    
[nginx01] [22/29] Fetching php74-fileinfo-7.4.19.txz: 100%  267 KiB 273.0kB/s    00:01    
[nginx01] [23/29] Fetching php74-exif-7.4.19.txz: 100%   27 KiB  28.2kB/s    00:01    
[nginx01] [24/29] Fetching php74-dom-7.4.19.txz: 100%   52 KiB  53.5kB/s    00:01    
[nginx01] [25/29] Fetching php74-ctype-7.4.19.txz: 100%    4 KiB   4.6kB/s    00:01    
[nginx01] [26/29] Fetching php74-7.4.19.txz: 100%    4 MiB   4.2MB/s    00:01    
[nginx01] [27/29] Fetching p5-CGI-4.52.txz: 100%  152 KiB 155.7kB/s    00:01    
[nginx01] [28/29] Fetching freshports-www-git-2.0.22_1.txz: 100%    2 MiB   2.4MB/s    00:01    
[nginx01] [29/29] Fetching bash-5.1.8.txz: 100%    2 MiB   1.6MB/s    00:01    
Checking integrity... done (1 conflicting)
  - freshports-www-git-2.0.22_1 conflicts with p5-freshports-scripts-git-2.0.16 on /usr/local/etc/freshports/config.sh.sample
Checking integrity... done (0 conflicting)
Conflicts with the existing packages have been found.
One more solver iteration is needed to resolve them.
The following 30 package(s) will be affected (of 0 checked):

Installed packages to be REMOVED:
	p5-freshports-scripts-git: 2.0.16

Installed packages to be UPGRADED:
	bash: 5.1.4_2 -> 5.1.8
	freshports-www-git: 2.0.20 -> 2.0.22_1
	p5-CGI: 4.51 -> 4.52
	php74: 7.4.18 -> 7.4.19
	php74-ctype: 7.4.18 -> 7.4.19
	php74-dom: 7.4.18 -> 7.4.19
	php74-exif: 7.4.18 -> 7.4.19
	php74-fileinfo: 7.4.18 -> 7.4.19
	php74-filter: 7.4.18 -> 7.4.19
	php74-gd: 7.4.18 -> 7.4.19
	php74-iconv: 7.4.18 -> 7.4.19
	php74-json: 7.4.18 -> 7.4.19
	php74-mbstring: 7.4.18 -> 7.4.19
	php74-opcache: 7.4.18 -> 7.4.19
	php74-pgsql: 7.4.18 -> 7.4.19
	php74-phar: 7.4.18 -> 7.4.19
	php74-posix: 7.4.18 -> 7.4.19
	php74-session: 7.4.18 -> 7.4.19
	php74-simplexml: 7.4.18 -> 7.4.19
	php74-tokenizer: 7.4.18 -> 7.4.19
	php74-xml: 7.4.18 -> 7.4.19
	php74-xmlreader: 7.4.18 -> 7.4.19
	php74-xmlwriter: 7.4.18 -> 7.4.19
	php74-zip: 7.4.18 -> 7.4.19
	php74-zlib: 7.4.18 -> 7.4.19
	postfix: 3.5.10,1 -> 3.6.0,1
	py38-freshports-fp-listen-git: 1.0.2_4 -> 1.0.2_5
	py38-six: 1.15.0 -> 1.16.0
	sqlite3: 3.35.5,1 -> 3.35.5_1,1

Number of packages to be removed: 1
Number of packages to be upgraded: 29

Proceed with this action? [y/N]: y
[nginx01] [1/30] Upgrading php74 from 7.4.18 to 7.4.19...
[nginx01] [1/30] Extracting php74-7.4.19: 100%
[nginx01] [2/30] Upgrading php74-dom from 7.4.18 to 7.4.19...
[nginx01] [2/30] Extracting php74-dom-7.4.19: 100%
[nginx01] [3/30] Upgrading php74-zlib from 7.4.18 to 7.4.19...
[nginx01] [3/30] Extracting php74-zlib-7.4.19: 100%
[nginx01] [4/30] Upgrading php74-xml from 7.4.18 to 7.4.19...
[nginx01] [4/30] Extracting php74-xml-7.4.19: 100%
[nginx01] [5/30] Upgrading php74-zip from 7.4.18 to 7.4.19...
[nginx01] [5/30] Extracting php74-zip-7.4.19: 100%
[nginx01] [6/30] Upgrading php74-xmlwriter from 7.4.18 to 7.4.19...
[nginx01] [6/30] Extracting php74-xmlwriter-7.4.19: 100%
[nginx01] [7/30] Upgrading php74-xmlreader from 7.4.18 to 7.4.19...
[nginx01] [7/30] Extracting php74-xmlreader-7.4.19: 100%
[nginx01] [8/30] Upgrading php74-tokenizer from 7.4.18 to 7.4.19...
[nginx01] [8/30] Extracting php74-tokenizer-7.4.19: 100%
[nginx01] [9/30] Upgrading php74-simplexml from 7.4.18 to 7.4.19...
[nginx01] [9/30] Extracting php74-simplexml-7.4.19: 100%
[nginx01] [10/30] Upgrading php74-session from 7.4.18 to 7.4.19...
[nginx01] [10/30] Extracting php74-session-7.4.19: 100%
[nginx01] [11/30] Upgrading php74-posix from 7.4.18 to 7.4.19...
[nginx01] [11/30] Extracting php74-posix-7.4.19: 100%
[nginx01] [12/30] Upgrading php74-phar from 7.4.18 to 7.4.19...
[nginx01] [12/30] Extracting php74-phar-7.4.19: 100%
[nginx01] [13/30] Upgrading php74-pgsql from 7.4.18 to 7.4.19...
[nginx01] [13/30] Extracting php74-pgsql-7.4.19: 100%
[nginx01] [14/30] Upgrading php74-opcache from 7.4.18 to 7.4.19...
[nginx01] [14/30] Extracting php74-opcache-7.4.19: 100%
[nginx01] [15/30] Upgrading php74-mbstring from 7.4.18 to 7.4.19...
[nginx01] [15/30] Extracting php74-mbstring-7.4.19: 100%
[nginx01] [16/30] Upgrading php74-json from 7.4.18 to 7.4.19...
[nginx01] [16/30] Extracting php74-json-7.4.19: 100%
[nginx01] [17/30] Upgrading php74-iconv from 7.4.18 to 7.4.19...
[nginx01] [17/30] Extracting php74-iconv-7.4.19: 100%
[nginx01] [18/30] Upgrading php74-gd from 7.4.18 to 7.4.19...
[nginx01] [18/30] Extracting php74-gd-7.4.19: 100%
[nginx01] [19/30] Upgrading php74-filter from 7.4.18 to 7.4.19...
[nginx01] [19/30] Extracting php74-filter-7.4.19: 100%
[nginx01] [20/30] Upgrading php74-fileinfo from 7.4.18 to 7.4.19...
[nginx01] [20/30] Extracting php74-fileinfo-7.4.19: 100%
[nginx01] [21/30] Upgrading php74-exif from 7.4.18 to 7.4.19...
[nginx01] [21/30] Extracting php74-exif-7.4.19: 100%
[nginx01] [22/30] Upgrading php74-ctype from 7.4.18 to 7.4.19...
[nginx01] [22/30] Extracting php74-ctype-7.4.19: 100%
[nginx01] [23/30] Deinstalling p5-freshports-scripts-git-2.0.16...
[nginx01] [23/30] Deleting files for p5-freshports-scripts-git-2.0.16: 100%
==> You should manually remove the "freshports" user. 
==> You should manually remove the "ingress" user. 
==> You should manually remove the "freshports" group 
==> You should manually remove the "ingress" group 
[nginx01] [24/30] Upgrading py38-freshports-fp-listen-git from 1.0.2_4 to 1.0.2_5...
===> Creating groups.
Using existing group 'freshports'.
===> Creating users
Using existing user 'freshports'.
===> Creating homedir(s)
[nginx01] [24/30] Extracting py38-freshports-fp-listen-git-1.0.2_5: 100%
[nginx01] [25/30] Upgrading sqlite3 from 3.35.5,1 to 3.35.5_1,1...
[nginx01] [25/30] Extracting sqlite3-3.35.5_1,1: 100%
[nginx01] [26/30] Upgrading py38-six from 1.15.0 to 1.16.0...
[nginx01] [26/30] Extracting py38-six-1.16.0: 100%
[nginx01] [27/30] Upgrading bash from 5.1.4_2 to 5.1.8...
[nginx01] [27/30] Extracting bash-5.1.8: 100%
[nginx01] [28/30] Upgrading postfix from 3.5.10,1 to 3.6.0,1...
===> Creating groups.
Using existing group 'mail'.
Using existing group 'maildrop'.
Using existing group 'postfix'.
===> Creating users
Using existing user 'postfix'.
===> Creating homedir(s)
[nginx01] [28/30] Extracting postfix-3.6.0,1: 100%

===============================================================
Postfix was *not* activated in /usr/local/etc/mail/mailer.conf! 

To finish installation run the following commands:

  mkdir -p /usr/local/etc/mail
  install -m 0644 /usr/local/share/postfix/mailer.conf.postfix /usr/local/etc/mail/mailer.conf
===============================================================

[nginx01] [29/30] Upgrading p5-CGI from 4.51 to 4.52...
[nginx01] [29/30] Extracting p5-CGI-4.52: 100%
[nginx01] [30/30] Upgrading freshports-www-git from 2.0.20 to 2.0.22_1...
===> Creating groups.
Using existing group 'freshports'.
Using existing group 'www'.
===> Creating users
Using existing user 'freshports'.
===> Creating homedir(s)
Using existing user 'www'.
[nginx01] [30/30] Extracting freshports-www-git-2.0.22_1: 100%
You may need to manually remove /usr/local/etc/php-fpm.conf if it is no longer needed.
You may need to manually remove /usr/local/etc/syslog.d/freshports.conf if it is no longer needed.
You may need to manually remove /usr/local/etc/postfix/main.cf if it is no longer needed.
=====
Message from postfix-3.6.0,1:

--
If you are upgrading from prior postfix version, please see the README
files for recommended changes to your configuration and additional
http://www.postfix.org/COMPATIBILITY_README.html

Incompatible change with postfix 3.5.x
==========================================
Internal protocols have changed. You need to "postfix stop" before
updating, or before backing out to an earlier release, otherwise
long-running daemons (pickup, qmgr, verify, tlsproxy, postscreen)
may fail to communicate with the rest of Postfix (warnings, timeouts).

The purpose of this change is to produce better error messages, for
example, when someone configures the discard daemon as a bounce
service in master.cf, or vice versa.

This change will break third-party code that implements a
Postfix-internal protocol such as qpsmtpd. Programs that depend on
Postfix internal details are not supported.
You may need to manually remove /usr/local/etc/freshports/constants.local.php if it is no longer needed.
You may need to manually remove /usr/local/etc/freshports/database.php if it is no longer needed.
You may need to manually remove /usr/local/etc/freshports/robots.txt if it is no longer needed.
You may need to manually remove /usr/local/etc/freshports/vhosts.conf.nginx if it is no longer needed.
You may need to manually remove /usr/local/etc/newsyslog.conf.d/freshports-www.conf if it is no longer needed.
You may need to manually remove /usr/local/etc/syslog.d/freshports-www.conf if it is no longer needed.

Then, the magic happens:

[root@nginx01 /]# pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 114 packages:

Installed packages to be REMOVED:
	apr: 1.7.0.1.6.1_1
	bash-completion: 2.11,2
	cvsps: 2.1_2
	db5: 5.3.28_7
	gdbm: 1.19
	git-lite: 2.31.1_1
	glib: 2.66.7_1,1
	gmp: 6.2.1
	gnupg: 2.3.1
	gnutls: 3.6.15
	libassuan: 2.5.5
	libgcrypt: 1.9.2_1
	libgit2: 1.1.0
	libgpg-error: 1.42
	libksba: 1.5.1
	libssh2: 1.9.0_3,3
	libtasn1: 4.16.0_1
	libxslt: 1.1.34_1
	nettle: 3.7.2_2
	npth: 1.6
	p11-kit: 0.23.22_1
	p5-Algorithm-C3: 0.11
	p5-Authen-NTLM: 1.09_1
	p5-Bit-Vector: 7.4
	p5-CGI: 4.52
	p5-Carp-Clan: 6.08
	p5-Class-C3: 0.35
	p5-Class-ISA: 0.36_1
	p5-Class-Method-Modifiers: 2.13
	p5-Class-Observable: 1.04_1
	p5-Clone: 0.45
	p5-DBD-Pg: 3.14.2
	p5-DBI: 1.643
	p5-Data-OptList: 0.110
	p5-Date-Calc: 6.4
	p5-Devel-StackTrace: 2.04
	p5-Digest-HMAC: 1.03_1
	p5-Email-Abstract: 3.008_1
	p5-Email-Address: 1.912
	p5-Email-Address-XS: 1.04
	p5-Email-Date-Format: 1.005
	p5-Email-MIME: 1.949
	p5-Email-MIME-ContentType: 1.026
	p5-Email-MIME-Encodings: 1.315_2
	p5-Email-MessageID: 1.406_1
	p5-Email-Sender: 1.300031_2
	p5-Email-Simple: 2.216
	p5-Encode-Locale: 1.05
	p5-Error: 0.17029
	p5-Exporter-Tiny: 1.002002
	p5-File-Listing: 6.14
	p5-HTML-Parser: 3.75
	p5-HTML-Tagset: 3.20_1
	p5-HTTP-Cookies: 6.10
	p5-HTTP-Daemon: 6.12
	p5-HTTP-Date: 6.05
	p5-HTTP-Message: 6.29
	p5-HTTP-Negotiate: 6.01_1
	p5-IO-HTML: 1.001_1
	p5-IO-Socket-INET6: 2.72_1
	p5-IO-Socket-SSL: 2.070
	p5-IO-String: 1.08_1
	p5-LWP-MediaTypes: 6.04
	p5-List-MoreUtils: 0.430
	p5-List-MoreUtils-XS: 0.430
	p5-MRO-Compat: 0.13
	p5-Module-Pluggable: 5.2
	p5-Module-Runtime: 0.016
	p5-Moo: 2.005004
	p5-MooX-Types-MooseLike: 0.29
	p5-Mozilla-CA: 20200520
	p5-Net-HTTP: 6.21
	p5-Net-SSLeay: 1.88
	p5-Params-Util: 1.102
	p5-Role-Tiny: 2.002004
	p5-Socket6: 0.29
	p5-Sub-Exporter: 0.987_1
	p5-Sub-Install: 0.928_1
	p5-Sub-Quote: 2.006006
	p5-Term-ReadKey: 2.38_1
	p5-Text-Unidecode: 1.30
	p5-Text-Wrapper: 1.05
	p5-Throwable: 0.200013
	p5-TimeDate: 2.33,1
	p5-Try-Tiny: 0.30
	p5-URI: 5.07
	p5-Unix-Syslog: 1.1_1
	p5-WWW-RobotRules: 6.02_1
	p5-XML-DOM: 1.44_1
	p5-XML-DOM-XPath: 0.14_1
	p5-XML-Node: 0.11_1
	p5-XML-Parser: 2.44
	p5-XML-RegExp: 0.04_1
	p5-XML-Writer: 0.900
	p5-XML-XPathEngine: 0.14_1
	p5-freshports-modules-git: 2.0.7
	p5-libwww: 6.53
	p5-libxml: 0.08_1
	p5-subversion: 1.14.1
	pinentry: 1.1.1
	pinentry-curses: 1.1.1
	py37-backports: 1
	py38-cached-property: 1.5.2
	py38-cffi: 1.14.5
	py38-pycparser: 2.20
	py38-pygit2: 1.5.0
	py38-six: 1.16.0
	python37: 3.7.10
	serf: 1.3.9_6
	sqlite3: 3.35.5_1,1
	subversion: 1.14.1
	tpm-emulator: 0.7.4_2
	trousers: 0.3.14_3
	utf8proc: 2.6.1

Number of packages to be removed: 114

The operation will free 298 MiB.

Proceed with deinstalling packages? [y/N]: y
[nginx01] [1/114] Deinstalling p5-subversion-1.14.1...
[nginx01] [1/114] Deleting files for p5-subversion-1.14.1: 100%
[nginx01] [2/114] Deinstalling p5-freshports-modules-git-2.0.7...
[nginx01] [2/114] Deleting files for p5-freshports-modules-git-2.0.7: 100%
==> You should manually remove the "freshports" user. 
==> You should manually remove the "ingress" user. 
==> You should manually remove the "freshports" group 
==> You should manually remove the "ingress" group 
[nginx01] [3/114] Deinstalling p5-XML-DOM-XPath-0.14_1...
[nginx01] [3/114] Deleting files for p5-XML-DOM-XPath-0.14_1: 100%
[nginx01] [4/114] Deinstalling subversion-1.14.1...
[nginx01] [4/114] Deleting files for subversion-1.14.1: 100%
[nginx01] [5/114] Deinstalling p5-XML-DOM-1.44_1...
[nginx01] [5/114] Deleting files for p5-XML-DOM-1.44_1: 100%
[nginx01] [6/114] Deinstalling gnupg-2.3.1...
[nginx01] [6/114] Deleting files for gnupg-2.3.1: 100%
[nginx01] [7/114] Deinstalling p5-Email-Sender-1.300031_2...
[nginx01] [7/114] Deleting files for p5-Email-Sender-1.300031_2: 100%
[nginx01] [8/114] Deinstalling p5-libwww-6.53...
[nginx01] [8/114] Deleting files for p5-libwww-6.53: 100%
[nginx01] [9/114] Deinstalling p5-CGI-4.52...
[nginx01] [9/114] Deleting files for p5-CGI-4.52: 100%
[nginx01] [10/114] Deinstalling gnutls-3.6.15...
[nginx01] [10/114] Deleting files for gnutls-3.6.15: 100%
[nginx01] [11/114] Deinstalling pinentry-1.1.1...
[nginx01] [11/114] Deleting files for pinentry-1.1.1: 100%
[nginx01] [12/114] Deinstalling py38-pygit2-1.5.0...
[nginx01] [12/114] Deleting files for py38-pygit2-1.5.0: 100%
[nginx01] [13/114] Deinstalling p5-Email-Abstract-3.008_1...
[nginx01] [13/114] Deleting files for p5-Email-Abstract-3.008_1: 100%
[nginx01] [14/114] Deinstalling p5-Throwable-0.200013...
[nginx01] [14/114] Deleting files for p5-Throwable-0.200013: 100%
[nginx01] [15/114] Deinstalling p5-HTML-Parser-3.75...
[nginx01] [15/114] Deleting files for p5-HTML-Parser-3.75: 100%
[nginx01] [16/114] Deinstalling p5-HTTP-Cookies-6.10...
[nginx01] [16/114] Deleting files for p5-HTTP-Cookies-6.10: 100%
[nginx01] [17/114] Deinstalling p5-HTTP-Daemon-6.12...
[nginx01] [17/114] Deleting files for p5-HTTP-Daemon-6.12: 100%
[nginx01] [18/114] Deinstalling p5-HTTP-Negotiate-6.01_1...
[nginx01] [18/114] Deleting files for p5-HTTP-Negotiate-6.01_1: 100%
[nginx01] [19/114] Deinstalling p5-Net-HTTP-6.21...
[nginx01] [19/114] Deleting files for p5-Net-HTTP-6.21: 100%
[nginx01] [20/114] Deinstalling serf-1.3.9_6...
[nginx01] [20/114] Deleting files for serf-1.3.9_6: 100%
[nginx01] [21/114] Deinstalling p11-kit-0.23.22_1...
[nginx01] [21/114] Deleting files for p11-kit-0.23.22_1: 100%
[nginx01] [22/114] Deinstalling trousers-0.3.14_3...
[nginx01] [22/114] Deleting files for trousers-0.3.14_3: 100%
==> You should manually remove the "_tss" user. 
==> You should manually remove the "_tss" group 
[nginx01] [23/114] Deinstalling pinentry-curses-1.1.1...
[nginx01] [23/114] Deleting files for pinentry-curses-1.1.1: 100%
[nginx01] [24/114] Deinstalling py38-cffi-1.14.5...
[nginx01] [24/114] Deleting files for py38-cffi-1.14.5: 100%
[nginx01] [25/114] Deinstalling libxslt-1.1.34_1...
[nginx01] [25/114] Deleting files for libxslt-1.1.34_1: 100%
[nginx01] [26/114] Deinstalling p5-MRO-Compat-0.13...
[nginx01] [26/114] Deleting files for p5-MRO-Compat-0.13: 100%
[nginx01] [27/114] Deinstalling p5-Date-Calc-6.4...
[nginx01] [27/114] Deleting files for p5-Date-Calc-6.4: 100%
[nginx01] [28/114] Deinstalling p5-Moo-2.005004...
[nginx01] [28/114] Deleting files for p5-Moo-2.005004: 100%
[nginx01] [29/114] Deinstalling p5-HTTP-Message-6.29...
[nginx01] [29/114] Deleting files for p5-HTTP-Message-6.29: 100%
[nginx01] [30/114] Deinstalling p5-Sub-Exporter-0.987_1...
[nginx01] [30/114] Deleting files for p5-Sub-Exporter-0.987_1: 100%
[nginx01] [31/114] Deinstalling p5-Email-MIME-1.949...
[nginx01] [31/114] Deleting files for p5-Email-MIME-1.949: 100%
[nginx01] [32/114] Deinstalling p5-File-Listing-6.14...
[nginx01] [32/114] Deleting files for p5-File-Listing-6.14: 100%
[nginx01] [33/114] Deinstalling p5-IO-Socket-SSL-2.070...
[nginx01] [33/114] Deleting files for p5-IO-Socket-SSL-2.070: 100%
[nginx01] [34/114] Deinstalling apr-1.7.0.1.6.1_1...
[nginx01] [34/114] Deleting files for apr-1.7.0.1.6.1_1: 100%
[nginx01] [35/114] Deinstalling bash-completion-2.11,2...
[nginx01] [35/114] Deleting files for bash-completion-2.11,2: 100%
[nginx01] [36/114] Deinstalling glib-2.66.7_1,1...
[nginx01] [36/114] Deleting files for glib-2.66.7_1,1: 100%
[nginx01] [37/114] Deinstalling nettle-3.7.2_2...
[nginx01] [37/114] Deleting files for nettle-3.7.2_2: 100%
[nginx01] [38/114] Deinstalling tpm-emulator-0.7.4_2...
[nginx01] [38/114] Deleting files for tpm-emulator-0.7.4_2: 100%
==> You should manually remove the "_tss" user. 
==> You should manually remove the "_tss" group 
[nginx01] [39/114] Deinstalling libassuan-2.5.5...
[nginx01] [39/114] Deleting files for libassuan-2.5.5: 100%
[nginx01] [40/114] Deinstalling libgcrypt-1.9.2_1...
[nginx01] [40/114] Deleting files for libgcrypt-1.9.2_1: 100%
[nginx01] [41/114] Deinstalling libgit2-1.1.0...
[nginx01] [41/114] Deleting files for libgit2-1.1.0: 100%
[nginx01] [42/114] Deinstalling libksba-1.5.1...
[nginx01] [42/114] Deleting files for libksba-1.5.1: 100%
[nginx01] [43/114] Deinstalling p5-Class-C3-0.35...
[nginx01] [43/114] Deleting files for p5-Class-C3-0.35: 100%
[nginx01] [44/114] Deinstalling p5-Authen-NTLM-1.09_1...
[nginx01] [44/114] Deleting files for p5-Authen-NTLM-1.09_1: 100%
[nginx01] [45/114] Deinstalling p5-Bit-Vector-7.4...
[nginx01] [45/114] Deleting files for p5-Bit-Vector-7.4: 100%
[nginx01] [46/114] Deinstalling p5-Class-Observable-1.04_1...
[nginx01] [46/114] Deleting files for p5-Class-Observable-1.04_1: 100%
[nginx01] [47/114] Deinstalling p5-Role-Tiny-2.002004...
[nginx01] [47/114] Deleting files for p5-Role-Tiny-2.002004: 100%
[nginx01] [48/114] Deinstalling p5-DBD-Pg-3.14.2...
[nginx01] [48/114] Deleting files for p5-DBD-Pg-3.14.2: 100%
[nginx01] [49/114] Deinstalling p5-Data-OptList-0.110...
[nginx01] [49/114] Deleting files for p5-Data-OptList-0.110: 100%
[nginx01] [50/114] Deinstalling p5-Email-Simple-2.216...
[nginx01] [50/114] Deleting files for p5-Email-Simple-2.216: 100%
[nginx01] [51/114] Deinstalling p5-Email-MIME-ContentType-1.026...
[nginx01] [51/114] Deleting files for p5-Email-MIME-ContentType-1.026: 100%
[nginx01] [52/114] Deinstalling p5-List-MoreUtils-0.430...
[nginx01] [52/114] Deleting files for p5-List-MoreUtils-0.430: 100%
[nginx01] [53/114] Deinstalling p5-HTTP-Date-6.05...
[nginx01] [53/114] Deleting files for p5-HTTP-Date-6.05: 100%
[nginx01] [54/114] Deinstalling p5-IO-Socket-INET6-2.72_1...
[nginx01] [54/114] Deleting files for p5-IO-Socket-INET6-2.72_1: 100%
[nginx01] [55/114] Deinstalling p5-MooX-Types-MooseLike-0.29...
[nginx01] [55/114] Deleting files for p5-MooX-Types-MooseLike-0.29: 100%
[nginx01] [56/114] Deinstalling p5-WWW-RobotRules-6.02_1...
[nginx01] [56/114] Deleting files for p5-WWW-RobotRules-6.02_1: 100%
[nginx01] [57/114] Deinstalling p5-XML-Node-0.11_1...
[nginx01] [57/114] Deleting files for p5-XML-Node-0.11_1: 100%
[nginx01] [58/114] Deinstalling p5-libxml-0.08_1...
[nginx01] [58/114] Deleting files for p5-libxml-0.08_1: 100%
[nginx01] [59/114] Deinstalling py37-backports-1...
[nginx01] [59/114] Deleting files for py37-backports-1: 100%
[nginx01] [60/114] Deinstalling py38-cached-property-1.5.2...
[nginx01] [60/114] Deleting files for py38-cached-property-1.5.2: 100%
[nginx01] [61/114] Deinstalling py38-pycparser-2.20...
[nginx01] [61/114] Deleting files for py38-pycparser-2.20: 100%
[nginx01] [62/114] Deinstalling cvsps-2.1_2...
[nginx01] [62/114] Deleting files for cvsps-2.1_2: 100%
[nginx01] [63/114] Deinstalling db5-5.3.28_7...
[nginx01] [63/114] Deleting files for db5-5.3.28_7: 100%
[nginx01] [64/114] Deinstalling git-lite-2.31.1_1...
[nginx01] [64/114] Deleting files for git-lite-2.31.1_1: 100%
==> You should manually remove the "git_daemon" user. 
==> You should manually remove the "git_daemon" group 
[nginx01] [65/114] Deinstalling gdbm-1.19...
[nginx01] [65/114] Deleting files for gdbm-1.19: 100%
[nginx01] [66/114] Deinstalling gmp-6.2.1...
[nginx01] [66/114] Deleting files for gmp-6.2.1: 100%
[nginx01] [67/114] Deinstalling sqlite3-3.35.5_1,1...
[nginx01] [67/114] Deleting files for sqlite3-3.35.5_1,1: 100%
[nginx01] [68/114] Deinstalling libgpg-error-1.42...
[nginx01] [68/114] Deleting files for libgpg-error-1.42: 100%
[nginx01] [69/114] Deinstalling libssh2-1.9.0_3,3...
[nginx01] [69/114] Deleting files for libssh2-1.9.0_3,3: 100%
[nginx01] [70/114] Deinstalling libtasn1-4.16.0_1...
[nginx01] [70/114] Deleting files for libtasn1-4.16.0_1: 100%
[nginx01] [71/114] Deinstalling npth-1.6...
[nginx01] [71/114] Deleting files for npth-1.6: 100%
[nginx01] [72/114] Deinstalling p5-Algorithm-C3-0.11...
[nginx01] [72/114] Deleting files for p5-Algorithm-C3-0.11: 100%
[nginx01] [73/114] Deinstalling p5-Carp-Clan-6.08...
[nginx01] [73/114] Deleting files for p5-Carp-Clan-6.08: 100%
[nginx01] [74/114] Deinstalling p5-Class-ISA-0.36_1...
[nginx01] [74/114] Deleting files for p5-Class-ISA-0.36_1: 100%
[nginx01] [75/114] Deinstalling p5-Class-Method-Modifiers-2.13...
[nginx01] [75/114] Deleting files for p5-Class-Method-Modifiers-2.13: 100%
[nginx01] [76/114] Deinstalling p5-Clone-0.45...
[nginx01] [76/114] Deleting files for p5-Clone-0.45: 100%
[nginx01] [77/114] Deinstalling p5-DBI-1.643...
[nginx01] [77/114] Deleting files for p5-DBI-1.643: 100%
[nginx01] [78/114] Deinstalling p5-Devel-StackTrace-2.04...
[nginx01] [78/114] Deleting files for p5-Devel-StackTrace-2.04: 100%
[nginx01] [79/114] Deinstalling p5-Digest-HMAC-1.03_1...
[nginx01] [79/114] Deleting files for p5-Digest-HMAC-1.03_1: 100%
[nginx01] [80/114] Deinstalling p5-Email-Address-1.912...
[nginx01] [80/114] Deleting files for p5-Email-Address-1.912: 100%
[nginx01] [81/114] Deinstalling p5-Email-Address-XS-1.04...
[nginx01] [81/114] Deleting files for p5-Email-Address-XS-1.04: 100%
[nginx01] [82/114] Deinstalling p5-Email-Date-Format-1.005...
[nginx01] [82/114] Deleting files for p5-Email-Date-Format-1.005: 100%
[nginx01] [83/114] Deinstalling p5-Email-MIME-Encodings-1.315_2...
[nginx01] [83/114] Deleting files for p5-Email-MIME-Encodings-1.315_2: 100%
[nginx01] [84/114] Deinstalling p5-Email-MessageID-1.406_1...
[nginx01] [84/114] Deleting files for p5-Email-MessageID-1.406_1: 100%
[nginx01] [85/114] Deinstalling p5-Encode-Locale-1.05...
[nginx01] [85/114] Deleting files for p5-Encode-Locale-1.05: 100%
[nginx01] [86/114] Deinstalling p5-Error-0.17029...
[nginx01] [86/114] Deleting files for p5-Error-0.17029: 100%
[nginx01] [87/114] Deinstalling p5-Exporter-Tiny-1.002002...
[nginx01] [87/114] Deleting files for p5-Exporter-Tiny-1.002002: 100%
[nginx01] [88/114] Deinstalling p5-HTML-Tagset-3.20_1...
[nginx01] [88/114] Deleting files for p5-HTML-Tagset-3.20_1: 100%
[nginx01] [89/114] Deinstalling p5-IO-HTML-1.001_1...
[nginx01] [89/114] Deleting files for p5-IO-HTML-1.001_1: 100%
[nginx01] [90/114] Deinstalling p5-IO-String-1.08_1...
[nginx01] [90/114] Deleting files for p5-IO-String-1.08_1: 100%
[nginx01] [91/114] Deinstalling p5-LWP-MediaTypes-6.04...
[nginx01] [91/114] Deleting files for p5-LWP-MediaTypes-6.04: 100%
[nginx01] [92/114] Deinstalling p5-List-MoreUtils-XS-0.430...
[nginx01] [92/114] Deleting files for p5-List-MoreUtils-XS-0.430: 100%
[nginx01] [93/114] Deinstalling p5-Module-Pluggable-5.2...
[nginx01] [93/114] Deleting files for p5-Module-Pluggable-5.2: 100%
[nginx01] [94/114] Deinstalling p5-Module-Runtime-0.016...
[nginx01] [94/114] Deleting files for p5-Module-Runtime-0.016: 100%
[nginx01] [95/114] Deinstalling p5-Mozilla-CA-20200520...
[nginx01] [95/114] Deleting files for p5-Mozilla-CA-20200520: 100%
[nginx01] [96/114] Deinstalling p5-Net-SSLeay-1.88...
[nginx01] [96/114] Deleting files for p5-Net-SSLeay-1.88: 100%
[nginx01] [97/114] Deinstalling p5-Params-Util-1.102...
[nginx01] [97/114] Deleting files for p5-Params-Util-1.102: 100%
[nginx01] [98/114] Deinstalling p5-Socket6-0.29...
[nginx01] [98/114] Deleting files for p5-Socket6-0.29: 100%
[nginx01] [99/114] Deinstalling p5-Sub-Install-0.928_1...
[nginx01] [99/114] Deleting files for p5-Sub-Install-0.928_1: 100%
[nginx01] [100/114] Deinstalling p5-Sub-Quote-2.006006...
[nginx01] [100/114] Deleting files for p5-Sub-Quote-2.006006: 100%
[nginx01] [101/114] Deinstalling p5-Term-ReadKey-2.38_1...
[nginx01] [101/114] Deleting files for p5-Term-ReadKey-2.38_1: 100%
[nginx01] [102/114] Deinstalling p5-Text-Unidecode-1.30...
[nginx01] [102/114] Deleting files for p5-Text-Unidecode-1.30: 100%
[nginx01] [103/114] Deinstalling p5-Text-Wrapper-1.05...
[nginx01] [103/114] Deleting files for p5-Text-Wrapper-1.05: 100%
[nginx01] [104/114] Deinstalling p5-TimeDate-2.33,1...
[nginx01] [104/114] Deleting files for p5-TimeDate-2.33,1: 100%
[nginx01] [105/114] Deinstalling p5-Try-Tiny-0.30...
[nginx01] [105/114] Deleting files for p5-Try-Tiny-0.30: 100%
[nginx01] [106/114] Deinstalling p5-URI-5.07...
[nginx01] [106/114] Deleting files for p5-URI-5.07: 100%
[nginx01] [107/114] Deinstalling p5-Unix-Syslog-1.1_1...
[nginx01] [107/114] Deleting files for p5-Unix-Syslog-1.1_1: 100%
[nginx01] [108/114] Deinstalling p5-XML-Parser-2.44...
[nginx01] [108/114] Deleting files for p5-XML-Parser-2.44: 100%
[nginx01] [109/114] Deinstalling p5-XML-RegExp-0.04_1...
[nginx01] [109/114] Deleting files for p5-XML-RegExp-0.04_1: 100%
[nginx01] [110/114] Deinstalling p5-XML-Writer-0.900...
[nginx01] [110/114] Deleting files for p5-XML-Writer-0.900: 100%
[nginx01] [111/114] Deinstalling p5-XML-XPathEngine-0.14_1...
[nginx01] [111/114] Deleting files for p5-XML-XPathEngine-0.14_1: 100%
[nginx01] [112/114] Deinstalling py38-six-1.16.0...
[nginx01] [112/114] Deleting files for py38-six-1.16.0: 100%
[nginx01] [113/114] Deinstalling python37-3.7.10...
[nginx01] [113/114] Deleting files for python37-3.7.10: 100%
[nginx01] [114/114] Deinstalling utf8proc-2.6.1...
[nginx01] [114/114] Deleting files for utf8proc-2.6.1: 100%
[root@nginx01 /]# 
Apr 132021
 

This post is mostly for personal use as I investigate this topic. It may take a few days and I need to keep notes.

Last week, FreshPorts went live with git commits. There are some outstanding issues, such as commits to branches, which I will catch up on in the weeks to come. During that update process, I noticed two tables which I thought were unused. I made a note for me to investigate them later. Those tables are:

freshports.devgit=# \dt commits_latest*
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | commits_latest       | table | postgres
 public | commits_latest_ports | table | postgres
(2 rows)

freshports.devgit=# 

Intent

I recall the intent of those tables: to retain a list of the most recent commits for quick display on the home page and in news feeds. I’ve gotten better at SQL since those days and I see they contain no data:

freshports.devgit=# select count(*) from commits_latest;
 count 
-------
     0
(1 row)

freshports.devgit=# select count(*) from commits_latest_ports;
 count 
-------
     0
(1 row)

freshports.devgit=# 

What do the tables look like

For the record, they look like.

freshports.devgit=# \d commits_latest
                         Table "public.commits_latest"
       Column       |           Type           | Collation | Nullable | Default 
--------------------+--------------------------+-----------+----------+---------
 commit_log_id      | integer                  |           |          | 
 commit_date_raw    | timestamp with time zone |           |          | 
 message_subject    | text                     |           |          | 
 message_id         | text                     |           |          | 
 committer          | text                     |           |          | 
 commit_description | text                     |           |          | 
 commit_date        | text                     |           |          | 
 commit_time        | text                     |           |          | 
 element_id         | integer                  |           |          | 
 element_name       | text                     |           |          | 
 revision_name      | text                     |           |          | 
 status             | character(1)             |           |          | 
 encoding_losses    | boolean                  |           |          | 
 element_pathname   | text                     |           |          | 

freshports.devgit=# \d commits_latest_ports
                     Table "public.commits_latest_ports"
    Column     |            Type             | Collation | Nullable | Default 
---------------+-----------------------------+-----------+----------+---------
 commit_log_id | integer                     |           | not null | 
 commit_date   | timestamp without time zone |           | not null | 
Indexes:
    "commits_latest_ports_pkey" PRIMARY KEY, btree (commit_log_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.devgit=# 

Does the website use it?

No, it’s not in use there:

[dan@devgit-nginx01:~/www] $ grep -ri commits_latest *
grep: configuration/robots.txt: Permission denied
grep: configuration/vhosts.conf: Permission denied
grep: configuration/vhosts.conf.nginx: No such file or directory
grep: configuration/virtualhost-common.conf: Permission denied
grep: configuration/status-config.php: No such file or directory
grep: configuration/database.php: Permission denied
docs/physical_database-2016-06-27.svg: >rel_commits_latest_ports_commit_log
docs/physical_database-2016-06-27.svg: >commits_latest
docs/physical_database-2016-06-27.svg: >commits_latest_ports
grep: www/robots.txt: Permission denied
[dan@devgit-nginx01:~/www] $ 

What about the backend?

No, it’s not there either:

[dan@devgit-ingress01:~/scripts] $ grep -ri commits_latest *
[dan@devgit-ingress01:~/scripts] $ cd ~/modules
[dan@devgit-ingress01:~/modules] $ grep -ri commits_latest *
grep: config.pm: Permission denied
[dan@devgit-ingress01:~/modules] $ 

What about the database

Perhaps it’s used by the store procedures, functions, relation integrity, etc:

[dan@pro02:~/src/freshports/database-schema-git] $ grep -ri commits_latest *
FreshPorts2.pdm:commits_latest
FreshPorts2.pdm:commits_latest
FreshPorts2.pdm:commits_latest
FreshPorts2.pdm:PK_COMMITS_LATEST
FreshPorts2.pdm:commits_latest_ports
FreshPorts2.pdm:commits_latest_ports
FreshPorts2.pdm:commits_latest_ports
createdb.sql:create table commits_latest
createdb.sql:create table commits_latest_ports
ri.txt:       WHERE (NOT (EXISTS (SELECT commits_latest_ports.commit_log_id
ri.txt:                             FROM latest_commits_ports commits_latest_ports
ri.txt:                            WHERE (commits_latest_ports.commit_log_id = new.commit_log_id)
sp.txt:       WHERE (NOT (EXISTS (SELECT commits_latest_ports.commit_log_id
sp.txt:                             FROM latest_commits_ports commits_latest_ports
sp.txt:                            WHERE (commits_latest_ports.commit_log_id = CommitLogID)
[dan@pro02:~/src/freshports/database-schema-git] $ 

Only ri.txt and sp.txt are relevant here:

From ri.txt:

CREATE OR REPLACE FUNCTION commit_log_ports_insert () RETURNS TRIGGER
AS '
BEGIN
   INSERT INTO latest_commits_ports (commit_log_id, commit_date)
              SELECT new.commit_log_id,
            (SELECT commit_log.commit_date
               FROM commit_log
              WHERE (commit_log.id = new.commit_log_id)
            )
       WHERE (NOT (EXISTS (SELECT commits_latest_ports.commit_log_id
                             FROM latest_commits_ports commits_latest_ports
                            WHERE (commits_latest_ports.commit_log_id = new.commit_log_id)
                           )
                   )
             );
   RETURN new;
END;'
LANGUAGE plpgsql;

  DROP TRIGGER IF EXISTS commit_log_ports_insert ON commit_log_ports;
CREATE TRIGGER commit_log_ports_insert
AFTER INSERT ON commit_log_ports
FOR EACH ROW
EXECUTE PROCEDURE commit_log_ports_insert ();

For each row added to commit_log_ports, invoke the trigger commit_log_ports_insert().

I know commit_log_ports is used. I don’t know about latest_commits_ports.

It seems we have two more tables to review:

freshports.devgit=# \dt latest_commits*
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | latest_commits       | table | postgres
 public | latest_commits_ports | table | postgres
(2 rows)

Those tables do seem to be used:

freshports.devgit=# select count(*) from latest_commits;
 count  
--------
 693798
(1 row)

freshports.devgit=# select count(*) from latest_commits_ports;
 count  
--------
 159794
(1 row)

From sp.txt:

CREATE OR REPLACE FUNCTION commit_log_ports_insert(int) RETURNS boolean
AS $$
DECLARE
        CommitLogID     ALIAS for $1;
BEGIN
   INSERT INTO latest_commits_ports (commit_log_id, commit_date)
              SELECT CommitLogID, 
            (SELECT commit_log.commit_date
               FROM commit_log
              WHERE (commit_log.id = CommitLogID)
            )
       WHERE (NOT (EXISTS (SELECT commits_latest_ports.commit_log_id
                             FROM latest_commits_ports commits_latest_ports
                            WHERE (commits_latest_ports.commit_log_id = CommitLogID)
                           )
                   )
             );

   RETURN FOUND;
END;$$

There is nothing here not already covered by <span class="file">ri.txt</span>.

LANGUAGE plpgsql;

Who uses latest_commits_ports?

I found this function:

CREATE OR REPLACE FUNCTION latest_commits_ports_anchor () RETURNS integer
    AS $$
  SELECT commit_log_id AS RESULT
    FROM latest_commits_ports
ORDER BY commit_log_id
   LIMIT 1;
$$
    LANGUAGE sql 
STABLE;

I question this function.

This is used by:

  1. LatestCommitsLarge()
  2. LatestCommitsLargeFiltered()
  3. LatestCommitsSmall()
  4. But those functions do stuff like this:

                          JOIN commit_log_ports_elements CLPE on CLPE.commit_log_id = LCPCL.commit_log_id
                                                             AND CLPE.commit_log_id > latest_commits_ports_anchor()
    

The above are used by:

  1. LatestCommits()
  2. LatestCommitsFiltered()

Look at classes/latest_commits.php:

                if (IsSet($this->Filter)) {
                        $sql = "select * from LatestCommitsFiltered($this->MaxNumberOfPorts, $this->UserID, '" . pg_escape_string($this->Filter) . "')";
                } else {
#                       $sql = "select * from LatestCommits($this->MaxNumberOfPorts, $this->UserID)";
                        $sql = "
  SELECT LC.*, STF.message AS stf_message
    FROM LatestCommits(" . pg_escape_string($this->MaxNumberOfPorts) . ", 0, '" . pg_escape_string($this->BranchName) . "') LC LEFT OUTER JOIN sanity_test_failures STF
      ON LC.commit_log_id = STF.commit_log_id
ORDER BY LC.commit_date_raw DESC, LC.category, LC.port, element_pathname";
                }

This is used by commits.php and filter.php (the latter of which has no links to to and it seems it can be deleted).

I also see reference to

Actions

While the above, I keep this section up to date as I went.

  1. Examine LatestCommitsLarge() vs LatestCommitsSmall() – do we still need both?
  2. LatestCommitsLarge is the one used by commits.php – does the AND CLPE.commit_log_id > latest_commits_ports_anchor() clause have useful effect?
  3. remove filter.php
  4. drop functions LatestCommitsFiltered() & LatestCommitsLargeFiltered()
  5. remove www/caching-files directory
  6. Reimplement www/caching-files/categories.php via a backend script, perhaps periodic
  7. look at scripts/cache-refresh.sh which hasn’t been invoked in production since 2017, according to the contents of the category_stats table which is displayed on categories.php page.
  8. The CategoryStatsUpdate() function might benefit from this:
    SELECT categories.name, categories.id               AS category_id,
             count(ports_active.id)      AS count,
             max(commit_log.commit_date) AS updated,
    		 case when categories.is_primary THEN '' ELSE '*' END
        FROM ports_categories, categories, ports_active left outer join commit_log on ( ports_active.last_commit_id = commit_log.id )
       WHERE ports_active.id = ports_categories.port_id
         AND categories.id   = ports_categories.category_id
    GROUP BY categories.id, categories.name, categories.description, is_primary, categories.element_id
    order by categories.name
    
Mar 302021
 

Hello,

The changeover from subversion to git for the FreeBSD project enters it’s final stage about three hours from now (as I type this). At 3:01 UTC on Mar 31 2021, the repository will become read-only. The full schedule for that process is in the FreeBSD wiki.

The first git commit is not expected until Apr 3rd.

That gives FreshPorts a couple of days to convert from subversion to git. The coding has been completed. The database needs to be modified, the new code installed, and configuration updates.

This change to FreshPorts represents the final stage of work which started over 13 months ago.

This process has had a few dry runs and it takes a few hours. Given the 3 day timeframe, the FreshPorts plans is laid out below.

  1. Drop the TTL because we will change to a new server.
  2. Disable user logins & disable account updates – a backup will be taken and any subsequent updates to production would not appear in the new database. About 12:30 UTC on 31 March
  3. Production will continue to serve up content during the updates.
  4. The new git-enabled website should be ready by about 20:00 UTC on 31 March.

The goals include:

  1. Apart from logins-disabled, you should not notice any change in service during the transition
  2. The plan is to reproduce the website on an AWS instance – this will be our first venture into that venue
  3. Service will gradually moved to the new website by use of multiple A and AAAA records.

Please watch our Twitter account and status page for updates.