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.

Mar 242023
 

The SQL injection issues, at least all that I could find, are fixed. The first alert was on March 18th. That went out via:

See also FreshSource code fixes.

Short version

  • everything we know about is fixed
  • we saw no evidence of data being obtained
  • we have no proof it was not obtained

The safest procedure: change your FreshPorts password. Anything you had set before Friday March 24 2023 09:49:20 UTC should be changed.

If you used the same login credentials somewhere else, you should change that too.

Long version

For you to be at risk, I would expect:

  • the data on FreshPorts to have been accessed (there is no evidence either way)
  • the hashes to have been cracked
  • you used the same FreshPorts login information somewhere else
  • The attacker then knows where that somewhere else is

From FreshPorts, the most valuable thing they might get is the list of packages you are tracking. We don’t have home addresses, phone numbers, or credit card information.

Sorry about this.

Mar 242023
 

In this post, I’ll talk about how someone was able to use SQL injection to spin up the database server to 100% utilization. I found the queries in the logs, duplicated the situation on dev, patched the code, and updated the website.

See also SQL inejection issues fixed.

In this post:

  • FreeBSD 13.1
  • PostgreSQL server 12.11 (running under RDS)

The first hint

On Thursday morning, I read an email:

From:AWS Notifications <no-reply@sns.amazonaws.com>
To:dan@langille.org
Subject:ALARM: "awsrds-pg01-CPU-Utilization" in US East (N. Virginia)
Date:Thursday, March 23, 2023 1:44 AM
Size:11 KB

You are receiving this email because your Amazon CloudWatch Alarm "awsrds-pg01-CPU-Utilization" in the 
US East (N. Virginia) region has entered the ALARM state, because "Threshold Crossed: 1 datapoint
[75.43258754427714 (23/03/23 04:43:00)] was greater than or equal to the threshold (75.0)." at 
"Thursday 23 March, 2023 05:44:40 UTC".

View this alarm in the AWS Management Console:
[redacted]

Alarm Details:
- Name:                       awsrds-pg01-CPU-Utilization
- Description:                
- State Change:               OK -> ALARM
- Reason for State Change:    Threshold Crossed: 1 datapoint [75.43258754427714 (23/03/23 
                              04:43:00)] was greater than or equal to the threshold (75.0).
- Timestamp:                  Thursday 23 March, 2023 05:44:40 UTC
- AWS Account:                782398408597
- Alarm Arn:                  arn:aws:cloudwatch:us-east-1:782398408597:alarm:awsrds-pg01-CPU-Utilization

Threshold:
- The alarm is in the ALARM state when the metric is GreaterThanOrEqualToThreshold 75.0 for
  at least 1 of the last 1 period(s) of 3600 seconds. 

Monitored Metric:
- MetricNamespace:                     AWS/RDS
- MetricName:                          CPUUtilization
- Dimensions:                          [DBInstanceIdentifier = pg01]
- Period:                              3600 seconds
- Statistic:                           Average
- Unit:                                not specified



State Change Actions:
- OK: 
- ALARM: [arn:aws:sns:us-east-1:782398408597:FreshPorts_AWS_DB_alarm]
- INSUFFICIENT_DATA: 


--
If you wish to stop receiving notifications from this topic, please click or visit the link below to unsubscribe:
[redacted]
Please do not reply directly to this email. If you have any questions or comments regarding this email,
please contact us at https://aws.amazon.com/support

Ahh, the website got slammed again. OK, I’ll look at that later.

… later

I went into the RDS console to see what was going on. This showed me it was mainly four queries:

Amazon Web Services web page showing four identical queries

Amazon Web Services web page showing four identical queries

That doesn’t show me the full details of the query.

Next, I connected directly to the database server via psql to find out what was being run.

See pg_stat_activity for more information.

[14:16 aws-1 dan ~] % psql -h [redacted].us-east-1.rds.amazonaws.com -U postgres freshports.org
Password for user postgres: 
psql (12.14, server 12.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

freshports.org=> select * from pg_stat_activity ;

What I saw was the usual queries with unusual content.

The screen shots

Here are the screen shots of what I saw. The webserver address is redacted.

pg_stat_activity output showing GENERATE_SERIES

pg_stat_activity output showing GENERATE_SERIES – image #1

pg_stat_activity output showing GENERATE_SERIES

pg_stat_activity output showing GENERATE_SERIES – image #2

pg_stat_activity output showing PG_SLEEP

pg_stat_activity output showing PG_SLEEP – image #3

pg_stat_activity output showing PG_SLEEP

pg_stat_activity output showing PG_SLEEP – image #4

pg_stat_activity output showing GENERATE_SERIES

pg_stat_activity output showing GENERATE_SERIES – image #5

Mitigate first

What I did first was kill those backend processes. The numbers used below are from the pid column in the screen shots above. See the Server Signaling Functions for more information.

freshports.org=>  select pg_cancel_backend(12759);
 pg_cancel_backend 
-------------------
 t
(1 row)

freshports.org=>  select pg_cancel_backend(13230);
 pg_cancel_backend 
-------------------
 t
(1 row)

freshports.org=>  select pg_cancel_backend(12918);
 pg_cancel_backend 
-------------------
 t
(1 row)

freshports.org=>  select pg_cancel_backend(13071);
 pg_cancel_backend 
-------------------
 t
(1 row)

Short term fix

It took me a while to figure out where this was being invoked from. I had assumed FreshPorts. I was wrong. I was FreshSource.

I put this at the top of commit.php:

<?php
if ($_SERVER['REMOTE_ADDR'] != '[redacted]') {
    echo 'We know. Thanks for letting us know. We will fix this up as soon as we can. :)';
    exit;
}

That IP address was me at home. That allowed me to test, but kept everyone else out.

The logs

I am not sure what alerted me to it being FreshSource. I think it was this command:

sudo grep e8cc50a10937e2df4b591c87217bf3dc6334c6d1 /var/log/nginx/*.log

You can see that commit id in the screen shots. Here are a few lines of what I saw:

[20:40 aws-1-nginx01 dan /usr/local/www/freshsource/www] % sudo grep commit.php /var/log/nginx/freshsource.org-access.log
141.98.83.236 - - [23/Mar/2023:04:26:07 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:22 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1 HTTP/1.1" 200 6506 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:33 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1&UeCa%3D3347%20AND%201%3D1%20UNION%20ALL%20SELECT%201%2CNULL%2C%27%3Cscript%3Ealert%28%22XSS%22%29%3C%2Fscript%3E%27%2Ctable_name%20FROM%20information_schema.tables%20WHERE%202%3E1--%2F%2A%2A%2F%3B%20EXEC%20xp_cmdshell%28%27cat%20..%2F..%2F..%2Fetc%2Fpasswd%27%29%23 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:34 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1&UeCa%3D3347%20AND%201%3D1%20UNION%20ALL%20SELECT%201%2CNULL%2C%27%3Cscript%3Ealert%28%22XSS%22%29%3C%2Fscript%3E%27%2Ctable_name%20FROM%20information_schema.tables%20WHERE%202%3E1--%2F%2A%2A%2F%3B%20EXEC%20xp_cmdshell%28%27cat%20..%2F..%2F..%2Fetc%2Fpasswd%27%29%23 HTTP/1.1" 200 6506 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:34 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:35 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1 HTTP/1.1" 200 6512 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:36 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%2C%28%27%22%2C%27%22%22%2C%28 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:37 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%2C%28%27%22%2C%27%22%22%2C%28 HTTP/1.1" 200 6156 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:37 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%27wTRixa%3C%27%22%3EnOcDhX HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:38 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%27wTRixa%3C%27%22%3EnOcDhX HTTP/1.1" 200 6154 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:45 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%29%20AND%209749%3D7105%20AND%20%288919%3D8919 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:46 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%29%20AND%209749%3D7105%20AND%20%288919%3D8919 HTTP/1.1" 200 4810 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:57 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%29%20AND%206118%3D6118%20AND%20%288487%3D8487 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:58 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%29%20AND%206118%3D6118%20AND%20%288487%3D8487 HTTP/1.1" 200 4810 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:26:58 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%20AND%205347%3D8036 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:27:00 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%20AND%205347%3D8036 HTTP/1.1" 200 4810 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:27:10 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%20AND%206118%3D6118 HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:27:10 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%20AND%206118%3D6118 HTTP/1.1" 200 4810 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"
141.98.83.236 - - [23/Mar/2023:04:27:11 +0000] "GET /commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%27%29%20AND%205149%3D1662%20AND%20%28%27XqbF%27%3D%27XqbF HTTP/1.1" 301 169 "-" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.12 Safari/532.2"

Here is the full record, about 1450 lines.

So what are they putting in there?

Here’s an example:

commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1%27%20AND%205291%3D%28SELECT%205291%20FROM%20PG_SLEEP%285%29%29--%20eEfz

Becomes:

commit.php?message_id=e8cc50a10937e2df4b591c87217bf3dc6334c6d1' AND 5291=(SELECT 5291 FROM PG_SLEEP(5))-- eEfz

That introduces a 5 second delay – enough for the attacker to know the query has been run.

That was the first query I found which worked. I used that as the starting point for my testing on dev. When setting out to solve a problem, I like to be able to reproduce it first. Otherwise, there is no proof that the problem has been solved.

The fix

The fix was to move the code into the 21st century. This was something written in June 2002. It now uses prepared statements, which go a long way towards avoiding SQL injections, and I think they are easier to use.

For example, here are some diffs from the fixed code:

-			   and  watch_list_element.watch_list_id = $WatchListID) ";
+			   and  watch_list_element.watch_list_id = $2) ";


...

-	$sql .= "
-	 where commit_log.message_id             = '$message_id'
+	$sql .= '
+	 where commit_log.message_id             = $1

...

-         $result = pg_exec($database, $sql);
+#         $result = pg_exec($database, $sql);
+         $result = pg_prepare($db, COMMIT_QUERY, $sql) or die('query failed ' . pg_last_error($db));
          if ($result) {
+            if ($WatchListID) {
+               $params = array($message_id, $WatchListID);
+            } else {I t
+               $params = array($message_id);
+            }
+            $result = pg_execute($db, COMMIT_QUERY, $params) or die('query failed ' . pg_last_error($db));
+         }
+         if ($result) { # yeah, I know there's a die above, but I didn't want to remove this if when going to prepared statements

This moves from using pg_exec (line 15) to pg_execute (line 16), which uses prepared statements (see the pg_prepare call on line 16). On lines 2 and 10 you can see the parameters to the query. WatchListID is optional and handled by the if on line 18.

I made my changes, tested them. Made more changes to other parts of FreshSource, pulling in updates from FreshPorts. The two are more similar now than before and share much more code and configuration.

This morning, I made some more changes after realizing some improvements could also apply to FreshPorts.

The changes were put production on March 24 2023 09:49:20 UTC.

Please change your password.

The things I missed

Back on Feb 13 of this year, I am sure the same thing happened. I took the service offline because it was so busy. That took about 10-15 minutes to fix. I assumed it was a fluke, the host got hit hard, and the database server could not keep up. I was alerted to the problem by a ‘slow website’ report.

I’m also convinced there was a previous incident (probably in Nov/Dec) which went totally unnoticed for several days.

Do the right thing

FreshSource and FreshPorts use the same database and the same logins. That’s why it’s relevant to FreshPort users. They are two different interfaces to the same data.

This code was outdated and long ignored. It should have been updated years ago. Sorry.

Jan 162023
 

Yesterday, an incident with a commit wound up taking up a lot of time to process on FreshPorts. The purpose of this post includes:

  1. document how the commit was handled on different nodes
  2. document the fixes for some nodes
  3. investigate how the code can be improved to detect such situations and abort

See also How to limit a jail.

The commit

The commit modified editors/ghostwriter/Makefile. It seemed straight forward. However, when processing the port, issues are found. I have a checked out copy of the ports tree on this host:

[empty dan /usr/ports/editors/ghostwriter] % sudo make -V PORTVERSION
make: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
2.2.0
[empty dan /usr/ports/editors/ghostwriter] % 

In this test, 17 iterations are repeated, then it returns to the command line. This doesn’t happen for all situation however. Case in point, make generate-plist:

[empty dan /usr/ports/editors/ghostwriter] % sudo make generate-plist
make: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5
make[1]: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5
make[2]: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5
^C*** Signal 2
*** Signal 2
*** Signal 2

[empty dan /usr/ports/editors/ghostwriter] % sudo make generate-plist

In this case, I stopped the process via ^C early in the process. If I let it run, I find many instances of generate-plist as demonstrated by this partial paste of the output.

[empty dan /usr/ports/editors/ghostwriter] % sudo make generate-plist
make: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5
make[1]: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[1]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[1]: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5
make[2]: "/usr/ports/Mk/bsd.port.mk" line 3406: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 4479: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5193: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[2]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[2]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored

...

make[115]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[115]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[115]: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5

At this point, I detach from that tmux session to check how many make generate-plist processes are running.

[detached (from session 0)]
[empty dan /usr/ports/editors/ghostwriter] % ps auwwx | grep generate
root      72821  3.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72739  3.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72780  3.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72698  3.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72903  3.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72575  3.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72616  3.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72657  3.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72862  3.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72493  3.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72534  3.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72411  3.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72452  3.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72370  3.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72329  3.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72288  3.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72206  2.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72247  2.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72134  2.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72081  2.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72040  2.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71875  2.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71917  2.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71958  2.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71999  2.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71792  2.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71833  2.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71710  2.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71751  2.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71628  2.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71669  2.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71577  2.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71495  2.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71536  2.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71413  1.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71454  1.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71372  1.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71290  1.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71331  1.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71208  1.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71249  1.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71085  1.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71126  1.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71167  1.5  0.0  12944  3304  2  S+J  14:26    0:00.01 make generate-plist
root      71003  1.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      71044  1.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70880  1.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70921  1.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70962  1.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70716  1.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70757  1.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70798  1.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70839  1.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70634  1.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70675  1.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70511  1.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70552  1.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70593  1.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70429  0.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70470  0.9  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70224  0.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70265  0.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70306  0.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70347  0.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70388  0.8  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70019  0.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70060  0.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70101  0.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70142  0.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      70183  0.7  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69855  0.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69896  0.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69937  0.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69978  0.6  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69731  0.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69773  0.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69814  0.5  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69526  0.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69567  0.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69608  0.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69649  0.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69690  0.4  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69362  0.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69403  0.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69444  0.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69485  0.3  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69198  0.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69239  0.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69280  0.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69321  0.2  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69075  0.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69116  0.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69157  0.1  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
dan       73150  0.0  0.0  12840  2328  0  S+J  14:26    0:00.00 grep generate
root      67091  0.0  0.0  18712  8204  2  I+J  14:26    0:00.01 sudo make generate-plist
root      67092  0.0  0.0  12944  3296  2  I+J  14:26    0:00.01 make generate-plist
root      67146  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67187  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67228  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67269  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67310  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67351  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67392  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67433  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67474  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67515  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67556  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67597  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67638  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67679  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67720  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67761  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67802  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67843  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67884  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67925  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      67966  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      68007  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      68048  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      68089  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      68130  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      68171  0.0  0.0  12944  3300  2  I+J  14:26    0:00.01 make generate-plist
root      68212  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68253  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68294  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68335  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68376  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68417  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68458  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68499  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68541  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68582  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68623  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68664  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68705  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68747  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68788  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68829  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68870  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68911  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68952  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      68993  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      69034  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72944  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      72985  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      73026  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      73067  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      73108  0.0  0.0  12944  3300  2  S+J  14:26    0:00.01 make generate-plist
root      73151  0.0  0.0  12944  2828  2  S+J  14:26    0:00.00 make generate-plist
[empty dan /usr/ports/editors/ghostwriter] % 

About 150 instances… what’s going on here? Recursion?

I rejoin my tmux session and stop the command:

make[195]: "/usr/ports/Mk/bsd.port.mk" line 5365: warning: duplicate script for target "/var/ports/usr/ports/editors/ghostwriter/work-qt5" ignored
make[195]: "/usr/ports/Mk/bsd.licenses.mk" line 769: warning: using previous script for "/var/ports/usr/ports/editors/ghostwriter/work-qt5" defined here
make[195]: Graph cycles through /var/ports/usr/ports/editors/ghostwriter/work-qt5
^C*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2
*** Signal 2

That’s about 190 Signal 2, I’m guessing one for every make generate-plist invocation.

The different nodes

Production was fixed first. The commit was never fully processed as you can see in this screenshot:

Screen shot of production website, the commit history for the port editors/ghostwriter is shown.

What’s wrong here? The commit at 21:05 does not show PORTVERSION and the refresh icon is displayed (that means commit processing did not conclude).

This was the case for two other nodes.

However, for dev, test, and stage, those nodes display this:

Screen shot of production website, the commit history for the port editors/ghostwriter is shown.

Sample screen shot from dev showing PORTVERSION 2.2.0 and there is no refresh icon – this commit appears to have completed processing.

My question is: why the difference?

Why?

Unless otherwise mentioned, in this section:

  • when I mention dev, I mean dev, test, and stage; the nodes which completed commit processing.
  • when I mention prod, I mean prod and the two other nodes which did not complete the commit processing.

This is what I know.

  1. Commit processing would start at about the same time on all nodes.
  2. Commit processing is automated and run from periodic scripts.
  3. Production was recovered first. dev was recovered last.
  4. Prod was rebooted.
  5. On dev, the retry queue is empty.
  6. On prod, there was an entry in the retry queue, but not on any other node, nor on dev

The last entry on prod for this commit was (I’ve split the line into two for easier reading).

generate_plist_command = /usr/local/bin/sudo /usr/sbin/jexec freshports /make-generate-plist.sh \
/usr/ports editors/ghostwriter 2>/tmp/FreshPorts.editors.ghostwriter.make-error.2023.1.15.21.6.17.30483

On dev, the jail was restarted. That also appears to have restarted processing the partially completed commit. The processing of each commit is logged to a file for that particular commit. The logs are overwritten when a commit is processed. Yeah, that sounds bad now. Those commits finished like this:

xml_munge_git.pm::SaveUpdateToDB --- start
GetExistingMessageID => sql=select id from commit_log where message_id = '6d8e63c53f121527898f3228fdecdc72a57022e3'
message 6d8e63c53f121527898f3228fdecdc72a57022e3 has already been added to the database
no commit id returned.  we'll just exit now shall we?

The date:

[dev-ingress01 dan ~freshports/message-queues/archive/2023_01/2023_01_15] % ls -l 2023.01.15.21.05.21.000000.6d8e63c53f121527898f3228fdecdc72a57022e3.log
-rw-r--r--  1 freshports  freshports  2731 2023.01.16 00:32 2023.01.15.21.05.21.000000.6d8e63c53f121527898f3228fdecdc72a57022e3.log
[dev-ingress01 dan ~freshports/message-queues/archive/2023_01/2023_01_15] % 

That time stamp is 00:32

Let’s check the database:

freshports.devgit=# select date_added from commit_log where message_id = '6d8e63c53f121527898f3228fdecdc72a57022e3';
          date_added           
-------------------------------
 2023-01-15 21:06:06.165977+00
(1 row)

freshports.devgit=# 

Checking all nodes, they all have similar log entries for this commit: message 6d8e63c53f121527898f3228fdecdc72a57022e3 has already been added to the database.

The commit was added to the database at 21:06, the commit was at 21:05 – therefore, this is the original commit processing, which someone got completely processed. That log is now completely gone. I checked snapshots; they’ve rolled over by now:

[slocum dan ~] % zfs list -r -t snapshot nvd/freshports/dev/message-queues
NAME                                                                        USED  AVAIL     REFER  MOUNTPOINT
nvd/freshports/dev/message-queues@autosnap_2023-01-16_10:01:30_hourly       161K      -     15.0M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_11:00:40_hourly        68K      -     15.7M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_12:01:12_hourly        66K      -     16.2M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_13:02:25_hourly       104K      -     16.7M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_14:01:00_hourly        78K      -     20.7M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_14:45:49_frequently    67K      -     21.0M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_15:00:40_hourly         0B      -     21.2M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_15:00:40_frequently     0B      -     21.2M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_15:15:39_frequently     0B      -     21.2M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_15:30:43_frequently     0B      -     21.2M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_15:45:34_frequently     0B      -     21.2M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_16:00:30_hourly         0B      -     21.4M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_16:00:30_frequently     0B      -     21.4M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_16:15:40_frequently     0B      -     21.3M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_16:30:57_frequently     0B      -     21.3M  -
nvd/freshports/dev/message-queues@autosnap_2023-01-16_16:45:27_frequently     0B      -     21.4M  -

Why processed twice

Why would the commit get processed twice? I suspect commit processing had not completely finished on any node. That would leave the commit in the incoming queue. The commit is not removed from that queue until processing is complete.

Why do we not just reprocess the commit in full? That is a decision made long ago. Commit processing can be system intensive, especially if it involves many ports. Usually, if a commit needs to be reprocessed, the existing commit is manually delete first, then manually injected back into the incoming queue. The fact that these are manual steps show how rare they occur.

Why no PORTVERSION

I don’t know why there is no PORTVERSION set on prod.

Fixing prod

This is how I fixed prod.

Let’s find the entry which needs fixing.

freshports.org=# select * from commit_log_ports where commit_log_id = (select id from commit_log where message_id = '6d8e63c53f121527898f3228fdecdc72a57022e3');
 commit_log_id | port_id | needs_refresh | port_version | port_revision | port_epoch | port_name_revision 
---------------+---------+---------------+--------------+---------------+------------+--------------------
        941999 |   49603 |             7 |              |               |            | 
(1 row)

freshports.org=# 

Here is the same entry from dev:

freshports.devgit=# select * from commit_log_ports where commit_log_id = (select id from commit_log where message_id = '6d8e63c53f121527898f3228fdecdc72a57022e3');
 commit_log_id | port_id | needs_refresh | port_version | port_revision | port_epoch | port_name_revision 
---------------+---------+---------------+--------------+---------------+------------+--------------------
        944770 |   49603 |             0 | 2.2.0        | 0             | 0          | ghostwriter-2.2.0
(1 row)

freshports.devgit=# 

This is the fix I ran:

freshports.org=# begin;
BEGIN
freshports.org=*# update commit_log_ports set needs_refresh = 0, port_version = '2.2.0', port_revision = 0, port_epoch = 0, port_name_revision = 'ghostwriter-2.2.0' where commit_log_id = 941999 and port_id = 49603;
UPDATE 1
freshports.org=*# select * from commit_log_ports where commit_log_id = (select id from commit_log where message_id = '6d8e63c53f121527898f3228fdecdc72a57022e3');
 commit_log_id | port_id | needs_refresh | port_version | port_revision | port_epoch | port_name_revision 
---------------+---------+---------------+--------------+---------------+------------+--------------------
        941999 |   49603 |             0 | 2.2.0        | 0             | 0          | ghostwriter-2.2.0
(1 row)

freshports.org=*# \d commit_log_ports
                Table "public.commit_log_ports"
       Column       |   Type   | Collation | Nullable | Default 
--------------------+----------+-----------+----------+---------
 commit_log_id      | integer  |           | not null | 
 port_id            | integer  |           | not null | 
 needs_refresh      | smallint |           | not null | 
 port_version       | text     |           |          | 
 port_revision      | text     |           |          | 
 port_epoch         | text     |           |          | 
 port_name_revision | text     |           |          | 
Indexes:
    "commit_log_ports_pkey" PRIMARY KEY, btree (commit_log_id, port_id)
    "commit_log_ports_needs_refresh_idx" btree (needs_refresh) WHERE needs_refresh <> 0
    "commit_log_ports_port_id" btree (port_id)
    "commit_log_ports_port_name_revision" btree (port_name_revision)
    "needs_refresh" btree (needs_refresh)
Foreign-key constraints:
    "$1" FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE
    "$2" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    commit_log_ports_insert AFTER INSERT ON commit_log_ports FOR EACH ROW EXECUTE FUNCTION commit_log_ports_insert()
freshports.org=*# commit;
COMMIT
freshports.org=# 

Then, to invalidate the cache, I touch the port:

freshports.org=# update ports set id = id where id = 49603;
UPDATE 1

That command should have been done within a commit. See above for where I grabbed 49603.

How does that invalidate the cache? A trigger invokes NOTIFY and processing LISTENing on the front end (website) removed the relevant cached items.

Possible improvements

There are things to consider:

  • Longer snapshot retention
  • Not overwriting logs, instead, only append. Before clowns start jumping on that: each commit has it’s own uniquely named log file.
  • Some kind of limits on the jobs I run within the jails – which would terminate the processing early, avoiding resource depletion
Jan 072023
 

The packages_raw gets reloaded. A lot. Every time a new set of packages is available from the FreeBSD packages table, data gets deleted and reloaded. So I manually vacuum the table. Well, it’s automated, as part of the code, but it doesn’t wait for the daily scripts.

I was seeing these errors on server r720-02:

2023-01-07 08:00:32.846 UTC [73161] WARNING:  skipping "packages_raw" --- only table or database owner can analyze it
2023-01-07 08:00:32.846 UTC [73161] CONTEXT:  SQL statement "analyze packages_raw"

I compared the permissions of the various database instances.

freshports.devgit=# \dp packages_raw
                                       Access privileges
 Schema |     Name     | Type  |       Access privileges        | Column privileges | Policies 
--------+--------------+-------+--------------------------------+-------------------+----------
 public | packages_raw | table | packaging=arwdDxt/packaging   +|                   | 
        |              |       | packager_dev=arwdDxt/packaging+|                   | 
        |              |       | rsyncer=r/packaging            |                   | 
(1 row)


freshports.testgit=# \dp packages_raw
                                       Access privileges
 Schema |     Name     | Type  |       Access privileges        | Column privileges | Policies 
--------+--------------+-------+--------------------------------+-------------------+----------
 public | packages_raw | table | packaging=arwdDxt/packaging   +|                   | 
        |              |       | packager_dev=arwdDxt/packaging+|                   | 
        |              |       | rsyncer=r/packaging            |                   | 
(1 row)


freshports.stagegit=# \dp packages_raw
                                       Access privileges
 Schema |     Name     | Type  |       Access privileges        | Column privileges | Policies 
--------+--------------+-------+--------------------------------+-------------------+----------
 public | packages_raw | table | packaging=arwdDxt/packaging   +|                   | 
        |              |       | packager_dev=arwdDxt/packaging+|                   | 
        |              |       | rsyncer=r/packaging            |                   | 
(1 row)

All the same on dev, test, and stage.

I should remove the explicit permissions for packager_dev on each of those databases. That user is a member of packaging.

Revoking extraneous permissions

As mentioned above, it’s time to remove those permissions:

freshports.testgit=# \dp packages_raw
                                       Access privileges
 Schema |     Name     | Type  |       Access privileges        | Column privileges | Policies 
--------+--------------+-------+--------------------------------+-------------------+----------
 public | packages_raw | table | packaging=arwdDxt/packaging   +|                   | 
        |              |       | packager_dev=arwdDxt/packaging+|                   | 
        |              |       | rsyncer=r/packaging            |                   | 
(1 row)

freshports.testgit=# revoke all ON packages_raw FROM packager_dev;
REVOKE
freshports.testgit=# \dp packages_raw
                                     Access privileges
 Schema |     Name     | Type  |      Access privileges      | Column privileges | Policies 
--------+--------------+-------+-----------------------------+-------------------+----------
 public | packages_raw | table | packaging=arwdDxt/packaging+|                   | 
        |              |       | rsyncer=r/packaging         |                   | 
(1 row)

freshports.testgit=# commit;
COMMIT

That was repeated on stage and dev.

Compare with the nodes not in my basement

prod is not the same as above:

[aws-1 dan ~] % psql freshports.org
Password for user postgres: 
psql (12.13, server 12.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

freshports.org=> \dp packages_raw
                                    Access privileges
 Schema |     Name     | Type  |     Access privileges     | Column privileges | Policies 
--------+--------------+-------+---------------------------+-------------------+----------
 public | packages_raw | table | postgres=arwdDxt/postgres+|                   | 
        |              |       | rsyncer=r/postgres       +|                   | 
        |              |       | packaging=arwd/postgres   |                   | 
(1 row)

freshports.org=> 

x8dtu matched prod.

root@x8dtu-pg01:/ # psql -U postgres freshports.org
psql (13.9)
Type "help" for help.

freshports.org=# \dp packages_raw
                                    Access privileges
 Schema |     Name     | Type  |     Access privileges     | Column privileges | Policies 
--------+--------------+-------+---------------------------+-------------------+----------
 public | packages_raw | table | postgres=arwdDxt/postgres+|                   | 
        |              |       | rsyncer=r/postgres       +|                   | 
        |              |       | packaging=arwd/postgres   |                   | 
(1 row)

So did r720-02 (the host generating the messages in question).

[r720-02-pg01 dan ~] % psql -U postgres freshports.org
psql (13.9)
Type "help" for help.

freshports.org=# \dp packages_raw
                                    Access privileges
 Schema |     Name     | Type  |     Access privileges     | Column privileges | Policies 
--------+--------------+-------+---------------------------+-------------------+----------
 public | packages_raw | table | postgres=arwdDxt/postgres+|                   | 
        |              |       | rsyncer=r/postgres       +|                   | 
        |              |       | packaging=arwd/postgres   |                   | 
(1 row)

freshports.org=# 

I noticed one difference: ownership.

This is dev, test, and stage:

freshports.stagegit=# \dt packages_raw
             List of relations
 Schema |     Name     | Type  |   Owner   
--------+--------------+-------+-----------
 public | packages_raw | table | packaging
(1 row)

This is aws-1 (i.e. prod), x8dtu, and r720-02

freshports.org=> \dt packages_raw
            List of relations
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | packages_raw | table | postgres
(1 row)

I did this on aws-1, x8dtu, and r720-02:

ALTER TABLE public.packages_raw OWNER TO packaging;
GRANT ALL ON TABLE public.packages_raw TO packaging;

The permissions on those hosts are now:

freshports.org=# \dp packages_raw
                                     Access privileges
 Schema |     Name     | Type  |      Access privileges      | Column privileges | Policies 
--------+--------------+-------+-----------------------------+-------------------+----------
 public | packages_raw | table | packaging=arwdDxt/packaging+|                   | 
        |              |       | rsyncer=r/packaging         |                   | 
(1 row)

Now it matches dev, test, and stage.

Jan 022023
 

When setting up a new FreshPorts ingress node, the repositories must be configured with tags so FreshPorts knows the last commit it has processed. This is the starting point each time FreshPorts goes to search for new commits.

This post about Processing commits on branches with git might help with background.

Those tags must be set or FreshPorts will not extract any commits. It is OK to process commits a second time; they will be ignored and the existing commit in the database will not be affected.

But first, the repos, we’ll come back to them

For processing incoming commits, these are the repos used:

[dev-ingress01 dan ~ingress/repos] % ls -l
total 8
drwxr-xr-x   7 ingress  ingress  11 2021.06.05 18:51 doc/
drwxr-xr-x  70 ingress  ingress  81 2022.12.05 15:20 ports/
drwxr-xr-x  27 ingress  ingress  44 2021.06.27 01:00 src/

There is another repo at /jails/freshports/usr/ports but that is used for extracting commit details and is not used to store FreshPorts tags.

Grabbing dates from the database

freshports.org=# select * from repo order by name, repo_hostname;
 id | name  |      description       |   repo_hostname    | path_to_repo | repository | db_root_prefix 
----+-------+------------------------+--------------------+--------------+------------+----------------
  8 | doc   | The FreeBSD doc tree   | cgit.freebsd.org   | /doc         | git        | /doc
  2 | doc   | The FreeBSD doc tree   | svnweb.freebsd.org | /doc         | subversion | 
  7 | ports | The FreeBSD Ports tree | cgit.freebsd.org   | /ports       | git        | /ports
  1 | ports | The FreeBSD Ports tree | svnweb.freebsd.org | /ports       | subversion | 
  9 | src   | The FreeBSD src tree   | cgit.freebsd.org   | /src         | git        | /base
  3 | src   | The FreeBSD src tree   | svnweb.freebsd.org | /base        | subversion | 
(6 rows)

We are dealing with git so we are only interested in lines 4,7, and 9.

What are the most recents commits for those repos?

freshports.org=# select max(commit_date), repo_id from commit_log where repo_id in 
                 (select id from repo where repository = 'git') group by repo_id order by repo_id;
          max           | repo_id 
------------------------+---------
 2022-12-16 01:44:49+00 |       7
 2022-12-15 19:59:09+00 |       8
 2022-12-15 20:06:26+00 |       9
(3 rows)

I am typing this on 2023-01-02 and working with a copy of production from mid-December.

Let’s get the commit hashes for those commit dates:

freshports.org=# select commit_date, message_id from commit_log where commit_date in (select max(commit_date) 
                 from commit_log where repo_id in (select id from repo where repository = 'git') 
                 group by repo_id order by repo_id);
      commit_date       |                message_id                
------------------------+------------------------------------------
 2022-12-15 19:59:09+00 | 54313f16d8a8f9113b1cc1596159d8fd602fc8c3
 2022-12-15 20:06:26+00 | 69542f26820b7edb8351398b36edda5299c1db56
 2022-12-16 01:44:49+00 | 47cc96f7fe91bd63dbe6e46026161454d9e59f34
(3 rows)

But that’s quite enough. We also process commits on branches. Let’s get those:

freshports.org=# select commit_date, message_id, branch_name from commit_log CL
join commit_log_branches CLB
on CL.id = CLB.commit_log_id 
join system_branch SB on SB.id = CLB.branch_id 
where commit_date in (select max(commit_date) from commit_log CL join commit_log_branches CLB
on CL.id = CLB.commit_log_id 
join system_branch SB on SB.id = CLB.branch_id
where repo_id in (select id from repo where repository = 'git') group by repo_id, branch_name order by repo_id, branch_name) order by branch_name;
      commit_date       |                  message_id                  | branch_name 
------------------------+----------------------------------------------+-------------
 2014-03-29 12:14:26+00 | 5f4d6e1d6b07bb34e5cbf866b4ef81e8ccdcb2da     | 2014Q1
 2014-06-28 13:14:00+00 | a3377806e58e030668a0406a35c434394a9333e1     | 2014Q2
 2014-10-01 03:35:40+00 | a0ccd6f83108ca7e16e64b6fd690dacefc5d0d58     | 2014Q3
 2015-11-15 21:14:57+00 | ce9f5d32567600a981517b7e35b2542a1114ece0     | 2014Q4
 2015-04-09 19:27:31+00 | 5bd325869bdeccff8037a64e2089b799abe798e0     | 2015Q1
 2015-07-23 16:24:25+00 | 7d7c2271f6c957574221e8746e5a356435cd114f     | 2015Q2
 2015-10-03 18:26:21+00 | a69940fb27f798fd49cfecb02cac3785412b76d0     | 2015Q3
 2015-12-30 10:20:19+00 | 38269ac51865d5d9bbaa23f6afcc2e6a6db5e9b1     | 2015Q4
 2016-03-31 18:39:28+00 | 847fef4d2acd28d745c9fe57194624dafc02e927     | 2016Q1
 2016-06-30 22:36:53+00 | 859c6d655ba8ec51549be3d8f52a302e7023d4ad     | 2016Q2
 2016-10-01 17:37:07+00 | dbe3dbb507e6a2068dee12ee7844edc3797e7dea     | 2016Q3
 2017-01-06 02:43:17+00 | 201701060243.v062hHhC027394@repo.freebsd.org | 2016Q4
 2017-01-06 02:43:17+00 | e113cbca02fc10dfb61a77d9c547550718cc7190     | 2016Q4
 2017-04-01 10:09:24+00 | 995d6409b4234b211b27bd14989a9c467f4d4b1a     | 2017Q1
 2017-04-01 10:09:24+00 | 201704011009.v31A9Oao076062@repo.freebsd.org | 2017Q1
 2017-07-20 09:21:03+00 | ee7b50bc8addebc46b27f99488b0f2c3a2ef6b94     | 2017Q2
 2017-07-20 09:21:03+00 | 201707200921.v6K9L3bp039228@repo.freebsd.org | 2017Q2
 2017-10-08 13:11:17+00 | a16d9e9f97932862f4433c03ee35493ec26fd29d     | 2017Q3
 2018-01-02 23:31:56+00 | f204b02d6dcefb91393ebeac39b3b6741b9ae5b1     | 2017Q4
 2018-06-10 10:30:41+00 | 8e67d346991ca584ffbe6ad23380c597e10d0ddf     | 2018Q1
 2018-07-01 14:06:28+00 | 06d6d83e775282797de36344c649a2c33a70861d     | 2018Q2
 2018-07-01 14:06:28+00 | 201807011406.w61E6SnC004420@repo.freebsd.org | 2018Q2
 2018-10-01 01:59:34+00 | 20f7d7640bc5014692574a4e09a7191dc021ecce     | 2018Q3
 2019-01-01 00:12:01+00 | 6948b132a446b2a84e021a6dd26b5ac7ea8e575a     | 2018Q4
 2019-03-30 22:06:36+00 | d2f5722e633ae238daba15792d4949b15d22c174     | 2019Q1
 2019-07-03 23:51:34+00 | ab797ee3a93f4bb4a3b22900257b9cbd91349887     | 2019Q2
 2019-10-01 06:00:59+00 | 201910010600.x9160xTh022892@repo.freebsd.org | 2019Q3
 2019-10-01 06:00:59+00 | d5d60820925216fc48c025190e0c30a79f52bf3b     | 2019Q3
 2019-12-31 16:06:20+00 | 201912311606.xBVG6K5g046485@repo.freebsd.org | 2019Q4
 2019-12-31 16:06:20+00 | f24745656dbbc619a6ee0360a5cababea241813c     | 2019Q4
 2020-03-31 17:46:10+00 | 28b4da349ddcc9e8493e8c3d013eb2fc4107098a     | 2020Q1
 2020-03-31 17:46:10+00 | 202003311746.02VHkAWa065169@repo.freebsd.org | 2020Q1
 2020-07-02 02:57:44+00 | 202007020257.0622viW4017065@repo.freebsd.org | 2020Q2
 2020-07-02 02:57:44+00 | 22e399d695717c14566f60255c72aae4c8c3d08c     | 2020Q2
 2020-09-30 21:44:10+00 | c0d44897151cceb0c4e63f2f7763b030f3019b66     | 2020Q3
 2021-01-03 03:56:17+00 | 202101030356.1033uHBj074526@repo.freebsd.org | 2020Q4
 2021-01-03 03:56:17+00 | c9104cb760e6d462d2e0a768cbbf0d95968012fa     | 2020Q4
 2021-04-10 12:53:34+00 | df5930d0c9fef97d429b0a43dad7d2168061b224     | 2021Q1
 2021-07-03 10:01:24+00 | d1da14bab7a800be62786aeb321b781179ea8b3f     | 2021Q2
 2021-10-03 00:38:11+00 | 5d6eb9d394c16d3e3ff4816090470feb64b39239     | 2021Q3
 2022-01-01 07:21:16+00 | 9f533ce8386ddc4b43d8b178e7c60f393f919a31     | 2021Q4
 2022-04-01 01:19:29+00 | 2c3e2897e25c082c3aade1f48af806a3aaf3839b     | 2022Q1
 2022-07-02 00:34:44+00 | ecc6a37c0d3d10f3a9212be604f70f720cf4b9d6     | 2022Q2
 2022-09-30 21:21:15+00 | bf76dbbb8eab3e8022705a7a56250b01b58f6d2f     | 2022Q3
 2022-12-15 17:48:39+00 | 3194a15871c26b6995584fcb018c2d3a2d0e3858     | 2022Q4
 2022-12-16 01:44:49+00 | 47cc96f7fe91bd63dbe6e46026161454d9e59f34     | head
 2022-12-15 19:59:09+00 | 54313f16d8a8f9113b1cc1596159d8fd602fc8c3     | head
 2022-12-15 20:06:26+00 | 69542f26820b7edb8351398b36edda5299c1db56     | head
 2021-04-06 15:32:13+00 | 713410cb2ca0a7dcdfd00fab203a16a0515b596f     | main
 2021-04-06 15:15:04+00 | f2f1ab39c04088ce53287528549e652cf68cee09     | main
(50 rows)

freshports.org=# 

We have some duplicate commits, where we have both the subversion and the git versions of a commit. The subversion commits have message_id ending in freebsd.org. This command removes the subversion commits and displays the repo name:

freshports.testgit=# select commit_date, message_id, branch_name, R.name from commit_log CL
join commit_log_branches CLB
on CL.id = CLB.commit_log_id 
join system_branch SB on SB.id = CLB.branch_id 
join repo R on CL.repo_id = R.id
where commit_date in (select max(commit_date) from commit_log CL join commit_log_branches CLB
on CL.id = CLB.commit_log_id 
join system_branch SB on SB.id = CLB.branch_id
where repo_id in (select id from repo where repository = 'git') group by repo_id, branch_name order by repo_id, branch_name) and message_id not like '%repo.freebsd.org' order by branch_name;
      commit_date       |                message_id                | branch_name | name  
------------------------+------------------------------------------+-------------+-------
 2014-03-29 12:14:26+00 | 5f4d6e1d6b07bb34e5cbf866b4ef81e8ccdcb2da | 2014Q1      | ports
 2014-06-28 13:14:00+00 | a3377806e58e030668a0406a35c434394a9333e1 | 2014Q2      | ports
 2014-10-01 03:35:40+00 | a0ccd6f83108ca7e16e64b6fd690dacefc5d0d58 | 2014Q3      | ports
 2015-11-15 21:14:57+00 | ce9f5d32567600a981517b7e35b2542a1114ece0 | 2014Q4      | ports
 2015-04-09 19:27:31+00 | 5bd325869bdeccff8037a64e2089b799abe798e0 | 2015Q1      | ports
 2015-07-23 16:24:25+00 | 7d7c2271f6c957574221e8746e5a356435cd114f | 2015Q2      | ports
 2015-10-03 18:26:21+00 | a69940fb27f798fd49cfecb02cac3785412b76d0 | 2015Q3      | ports
 2015-12-30 10:20:19+00 | 38269ac51865d5d9bbaa23f6afcc2e6a6db5e9b1 | 2015Q4      | ports
 2016-03-31 18:39:28+00 | 847fef4d2acd28d745c9fe57194624dafc02e927 | 2016Q1      | ports
 2016-06-30 22:36:53+00 | 859c6d655ba8ec51549be3d8f52a302e7023d4ad | 2016Q2      | ports
 2016-10-01 17:37:07+00 | dbe3dbb507e6a2068dee12ee7844edc3797e7dea | 2016Q3      | ports
 2017-01-06 02:43:17+00 | e113cbca02fc10dfb61a77d9c547550718cc7190 | 2016Q4      | ports
 2017-04-01 10:09:24+00 | 995d6409b4234b211b27bd14989a9c467f4d4b1a | 2017Q1      | ports
 2017-07-20 09:21:03+00 | ee7b50bc8addebc46b27f99488b0f2c3a2ef6b94 | 2017Q2      | ports
 2017-10-08 13:11:17+00 | a16d9e9f97932862f4433c03ee35493ec26fd29d | 2017Q3      | ports
 2018-01-02 23:31:56+00 | f204b02d6dcefb91393ebeac39b3b6741b9ae5b1 | 2017Q4      | ports
 2018-06-10 10:30:41+00 | 8e67d346991ca584ffbe6ad23380c597e10d0ddf | 2018Q1      | ports
 2018-07-01 14:06:28+00 | 06d6d83e775282797de36344c649a2c33a70861d | 2018Q2      | ports
 2018-10-01 01:59:34+00 | 20f7d7640bc5014692574a4e09a7191dc021ecce | 2018Q3      | ports
 2019-01-01 00:12:01+00 | 6948b132a446b2a84e021a6dd26b5ac7ea8e575a | 2018Q4      | ports
 2019-03-30 22:06:36+00 | d2f5722e633ae238daba15792d4949b15d22c174 | 2019Q1      | ports
 2019-07-03 23:51:34+00 | ab797ee3a93f4bb4a3b22900257b9cbd91349887 | 2019Q2      | ports
 2019-10-01 06:00:59+00 | d5d60820925216fc48c025190e0c30a79f52bf3b | 2019Q3      | ports
 2019-12-31 16:06:20+00 | f24745656dbbc619a6ee0360a5cababea241813c | 2019Q4      | ports
 2020-03-31 17:46:10+00 | 28b4da349ddcc9e8493e8c3d013eb2fc4107098a | 2020Q1      | ports
 2020-07-02 02:57:44+00 | 22e399d695717c14566f60255c72aae4c8c3d08c | 2020Q2      | ports
 2020-09-30 21:44:10+00 | c0d44897151cceb0c4e63f2f7763b030f3019b66 | 2020Q3      | ports
 2021-01-03 03:56:17+00 | c9104cb760e6d462d2e0a768cbbf0d95968012fa | 2020Q4      | ports
 2021-04-10 12:53:34+00 | df5930d0c9fef97d429b0a43dad7d2168061b224 | 2021Q1      | ports
 2021-07-03 10:01:24+00 | d1da14bab7a800be62786aeb321b781179ea8b3f | 2021Q2      | ports
 2021-10-03 00:38:11+00 | 5d6eb9d394c16d3e3ff4816090470feb64b39239 | 2021Q3      | ports
 2022-01-01 07:21:16+00 | 9f533ce8386ddc4b43d8b178e7c60f393f919a31 | 2021Q4      | ports
 2022-04-01 01:19:29+00 | 2c3e2897e25c082c3aade1f48af806a3aaf3839b | 2022Q1      | ports
 2022-07-02 00:34:44+00 | ecc6a37c0d3d10f3a9212be604f70f720cf4b9d6 | 2022Q2      | ports
 2022-09-30 21:21:15+00 | bf76dbbb8eab3e8022705a7a56250b01b58f6d2f | 2022Q3      | ports
 2022-12-31 00:28:03+00 | 78bbde9c877db24e936f3cdd9c371de2dae4ffcc | 2022Q4      | ports
 2023-01-02 04:17:27+00 | a1f28ec729f7491da8607e8eeaee1b0f547c60d0 | head        | src
 2023-01-02 16:37:13+00 | 06c71031f8cff7d40944080acd69355df8b22f8d | head        | ports
 2023-01-02 01:37:05+00 | 1a4668dc587f92c4a37bf5897fd9a43954d8b48e | head        | doc
 2021-04-06 20:54:18+00 | 2c1a6b3977da8446ba2232eb0d86fe713cbba65a | main        | doc
 2021-04-06 21:27:16+00 | ac503c194cd8e9dbef5c120e87f9521e1a89003a | main        | src
(41 rows)

freshports.testgit=# 

What is the objective?

What I want is commands like this:

git tag -m 'last known commit of origin/main' -f freshports/origin/main 47cc96f7fe91bd63dbe6e46026161454d9e59f34

This should accomplish that:

freshports.org=# select 'git tag -m ''last known commit of freshports/origin/' || branch_name || ''' -f freshports/origin/' || branch_name || ' ' || message_id from commit_log CL
join commit_log_branches CLB
on CL.id = CLB.commit_log_id 
join system_branch SB on SB.id = CLB.branch_id 
join repo R on CL.repo_id = R.id
where commit_date in (select max(commit_date) from commit_log CL join commit_log_branches CLB
on CL.id = CLB.commit_log_id 
join system_branch SB on SB.id = CLB.branch_id
where repo_id in (select id from repo where repository = 'git') group by repo_id, branch_name order by repo_id, branch_name) and message_id not like '%repo.freebsd.org' order by branch_name;
                                                            ?column?                                                             
---------------------------------------------------------------------------------------------------------------------------------
 git tag -m 'last known commit of freshports/origin/2014Q1' -f freshports/origin/2014Q1 5f4d6e1d6b07bb34e5cbf866b4ef81e8ccdcb2da
 git tag -m 'last known commit of freshports/origin/2014Q2' -f freshports/origin/2014Q2 a3377806e58e030668a0406a35c434394a9333e1
 git tag -m 'last known commit of freshports/origin/2014Q3' -f freshports/origin/2014Q3 a0ccd6f83108ca7e16e64b6fd690dacefc5d0d58
 git tag -m 'last known commit of freshports/origin/2014Q4' -f freshports/origin/2014Q4 ce9f5d32567600a981517b7e35b2542a1114ece0
 git tag -m 'last known commit of freshports/origin/2015Q1' -f freshports/origin/2015Q1 5bd325869bdeccff8037a64e2089b799abe798e0
 git tag -m 'last known commit of freshports/origin/2015Q2' -f freshports/origin/2015Q2 7d7c2271f6c957574221e8746e5a356435cd114f
 git tag -m 'last known commit of freshports/origin/2015Q3' -f freshports/origin/2015Q3 a69940fb27f798fd49cfecb02cac3785412b76d0
 git tag -m 'last known commit of freshports/origin/2015Q4' -f freshports/origin/2015Q4 38269ac51865d5d9bbaa23f6afcc2e6a6db5e9b1
 git tag -m 'last known commit of freshports/origin/2016Q1' -f freshports/origin/2016Q1 847fef4d2acd28d745c9fe57194624dafc02e927
 git tag -m 'last known commit of freshports/origin/2016Q2' -f freshports/origin/2016Q2 859c6d655ba8ec51549be3d8f52a302e7023d4ad
 git tag -m 'last known commit of freshports/origin/2016Q3' -f freshports/origin/2016Q3 dbe3dbb507e6a2068dee12ee7844edc3797e7dea
 git tag -m 'last known commit of freshports/origin/2016Q4' -f freshports/origin/2016Q4 e113cbca02fc10dfb61a77d9c547550718cc7190
 git tag -m 'last known commit of freshports/origin/2017Q1' -f freshports/origin/2017Q1 995d6409b4234b211b27bd14989a9c467f4d4b1a
 git tag -m 'last known commit of freshports/origin/2017Q2' -f freshports/origin/2017Q2 ee7b50bc8addebc46b27f99488b0f2c3a2ef6b94
 git tag -m 'last known commit of freshports/origin/2017Q3' -f freshports/origin/2017Q3 a16d9e9f97932862f4433c03ee35493ec26fd29d
 git tag -m 'last known commit of freshports/origin/2017Q4' -f freshports/origin/2017Q4 f204b02d6dcefb91393ebeac39b3b6741b9ae5b1
 git tag -m 'last known commit of freshports/origin/2018Q1' -f freshports/origin/2018Q1 8e67d346991ca584ffbe6ad23380c597e10d0ddf
 git tag -m 'last known commit of freshports/origin/2018Q2' -f freshports/origin/2018Q2 06d6d83e775282797de36344c649a2c33a70861d
 git tag -m 'last known commit of freshports/origin/2018Q3' -f freshports/origin/2018Q3 20f7d7640bc5014692574a4e09a7191dc021ecce
 git tag -m 'last known commit of freshports/origin/2018Q4' -f freshports/origin/2018Q4 6948b132a446b2a84e021a6dd26b5ac7ea8e575a
 git tag -m 'last known commit of freshports/origin/2019Q1' -f freshports/origin/2019Q1 d2f5722e633ae238daba15792d4949b15d22c174
 git tag -m 'last known commit of freshports/origin/2019Q2' -f freshports/origin/2019Q2 ab797ee3a93f4bb4a3b22900257b9cbd91349887
 git tag -m 'last known commit of freshports/origin/2019Q3' -f freshports/origin/2019Q3 d5d60820925216fc48c025190e0c30a79f52bf3b
 git tag -m 'last known commit of freshports/origin/2019Q4' -f freshports/origin/2019Q4 f24745656dbbc619a6ee0360a5cababea241813c
 git tag -m 'last known commit of freshports/origin/2020Q1' -f freshports/origin/2020Q1 28b4da349ddcc9e8493e8c3d013eb2fc4107098a
 git tag -m 'last known commit of freshports/origin/2020Q2' -f freshports/origin/2020Q2 22e399d695717c14566f60255c72aae4c8c3d08c
 git tag -m 'last known commit of freshports/origin/2020Q3' -f freshports/origin/2020Q3 c0d44897151cceb0c4e63f2f7763b030f3019b66
 git tag -m 'last known commit of freshports/origin/2020Q4' -f freshports/origin/2020Q4 c9104cb760e6d462d2e0a768cbbf0d95968012fa
 git tag -m 'last known commit of freshports/origin/2021Q1' -f freshports/origin/2021Q1 df5930d0c9fef97d429b0a43dad7d2168061b224
 git tag -m 'last known commit of freshports/origin/2021Q2' -f freshports/origin/2021Q2 d1da14bab7a800be62786aeb321b781179ea8b3f
 git tag -m 'last known commit of freshports/origin/2021Q3' -f freshports/origin/2021Q3 5d6eb9d394c16d3e3ff4816090470feb64b39239
 git tag -m 'last known commit of freshports/origin/2021Q4' -f freshports/origin/2021Q4 9f533ce8386ddc4b43d8b178e7c60f393f919a31
 git tag -m 'last known commit of freshports/origin/2022Q1' -f freshports/origin/2022Q1 2c3e2897e25c082c3aade1f48af806a3aaf3839b
 git tag -m 'last known commit of freshports/origin/2022Q2' -f freshports/origin/2022Q2 ecc6a37c0d3d10f3a9212be604f70f720cf4b9d6
 git tag -m 'last known commit of freshports/origin/2022Q3' -f freshports/origin/2022Q3 bf76dbbb8eab3e8022705a7a56250b01b58f6d2f
 git tag -m 'last known commit of freshports/origin/2022Q4' -f freshports/origin/2022Q4 3194a15871c26b6995584fcb018c2d3a2d0e3858
 git tag -m 'last known commit of freshports/origin/head' -f freshports/origin/head 54313f16d8a8f9113b1cc1596159d8fd602fc8c3
 git tag -m 'last known commit of freshports/origin/head' -f freshports/origin/head 47cc96f7fe91bd63dbe6e46026161454d9e59f34
 git tag -m 'last known commit of freshports/origin/head' -f freshports/origin/head 69542f26820b7edb8351398b36edda5299c1db56
 git tag -m 'last known commit of freshports/origin/main' -f freshports/origin/main 713410cb2ca0a7dcdfd00fab203a16a0515b596f
 git tag -m 'last known commit of freshports/origin/main' -f freshports/origin/main f2f1ab39c04088ce53287528549e652cf68cee09
(41 rows)

freshports.org=#    

That might just work.

Dec 252022
 

The commits.php page has a long history dating back to 2006 in GitHub and before that in another repo.

Soon, it is going away. The justification is in issue 337: it’s not used much, and it’s not doing as it should (loading the last 100 commits).

In the early days, the front page showed only a few commits. Loading the commits from the database was intensive. Caching hadn’t been implemented yet. I’m sure better queries had not been developed. If you wanted to see more, you looking at /commits.php instead.

Now it’s going away. First with a redirect to / – after a few weeks of that, it’ll be removed from the code entirely.

RIP>

Dec 232022
 

This post documents how I’m changing the search methods for pkg-message. It uses full-text search, but instead of using a special column, I’m going to use an index. I will elaborate.

When searching pkg-message was added to the website, a different approach was taken than in the past.
In Converting a column-based expression to a row based solution, you can see an index such as “generate_plist_installed_file_gin_idx” gin (to_tsvector(‘english’::regconfig, installed_file)) being used.

However, in When tsvector was working as expected on most hosts, but not one you can see that the tsvector values are being stored in separate columns (pkgmessage_textsearchable and pkgmessage_textsearchable2) using the Generated Columns feature of PostgreSQL. Then an index over that column is created. They look like this:

    "ports_pkgmessage_textsearchable2_idx" gin (pkgmessage_textsearchable2)
    "ports_pkgmessage_textsearchable_idx" gin (pkgmessage_textsearchable)

The columns would be used like this in a query:

SELECT DISTINCT id as port_id
  FROM ports
 WHERE pkgmessage_textsearchable  @@ websearch_to_tsquery($1) OR
       pkgmessage_textsearchable2 @@ websearch_to_tsquery($1)

It was suggested that I could do this instead:

  1. drop the two auto-generated columns
  2. drop the two gin indexes
  3. create two new indexes based on the columns

The query could then be on the original columns:

SELECT DISTINCT id as port_id
  FROM ports
 WHERE pkgmessage @@ websearch_to_tsquery('english', $1) OR
       pkgmessage @@ websearch_to_tsquery('simple', $1)

The rest of this post documents the changes.

Database modification

begin;
drop index ports_pkgmessage_textsearchable_idx;
drop index ports_pkgmessage_textsearchable2_idx;
alter table ports drop column pkgmessage_textsearchable;
alter table ports drop column pkgmessage_textsearchable2;
create index ports_pkgmessage_english_idx on ports using gin (to_tsvector('english'::regconfig, pkgmessage));
create index ports_pkgmessage_simple_idx  on ports using gin (to_tsvector('simple'::regconfig, pkgmessage));
commit;

Code changes

[dev-nginx01 dan ~/www] % git diff                                                                                                                        21:13:21
diff --git a/classes/ports_by_pkg_message.php b/classes/ports_by_pkg_message.php
index ebea978d..1681c695 100644
--- a/classes/ports_by_pkg_message.php
+++ b/classes/ports_by_ .php
@@ -27,8 +27,8 @@ class PortsByPkgMessage extends Port {
     FROM
         ports
     WHERE
-        pkgmessage_textsearchable  @@ websearch_to_tsquery($1) OR
-        pkgmessage_textsearchable2 @@ websearch_to_tsquery($1)
+        pkgmessage @@ websearch_to_tsquery('english', $1) OR
+        pkgmessage @@ websearch_to_tsquery('simple',  $1)
 )';
 
        function __construct($dbh) {

That’s it

That was tremendously simple. Let’s try the stage database next and see how that affects table size.

freshports.stagegit=# select pg_indexes_size('ports');
 pg_indexes_size 
-----------------
        36372480
(1 row)

freshports.stagegit=# select pg_table_size('ports');
 pg_table_size 
---------------
     275243008
(1 row)

freshports.stagegit=# vacuum analyse ports;
VACUUM
freshports.stagegit=# select pg_table_size('ports');
 pg_table_size 
---------------
     275243008
(1 row)

freshports.stagegit=# select pg_indexes_size('ports');
 pg_indexes_size 
-----------------
        36397056
(1 row)

Let’s do a vacuum first, because we can.

Now for the changes:

freshports.stagegit=# begin;
BEGIN
freshports.stagegit=# drop index ports_pkgmessage_textsearchable_idx;
drop index ports_pkgmessage_textsearchable2_idx;
alter table ports drop column pkgmessage_textsearchable;
alter table ports drop column pkgmessage_textsearchable2;
create index ports_pkgmessage_english_idx on ports using gin (to_tsvector('english'::regconfig, pkgmessage));
create index ports_pkgmessage_simple_idx  on ports using gin (to_tsvector('simple'::regconfig, pkgmessage));
DROP INDEX
DROP INDEX
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
freshports.stagegit=# select pg_table_size('ports');
 pg_table_size 
---------------
     275243008
(1 row)

freshports.stagegit=# select pg_indexes_size('ports');
 pg_indexes_size 
-----------------
        33742848
(1 row)
freshports.stagegit=# commit;
COMMIT

Now a vacuum:

freshports.stagegit=# vacuum analyse ports;
VACUUM
freshports.stagegit=# select pg_indexes_size('ports');
 pg_indexes_size 
-----------------
        36397056
(1 row)

freshports.stagegit=# select pg_table_size('ports');
 pg_table_size 
---------------
     275243008
(1 row)

The index size was unchanged. Same with the table size. So. No big gain, no big loss.

Except that now I have two fewer columns.

Dec 182022
 

One of the primary uses for FreshPorts is searching various characteristics of the ports. One highly used search feature is pkg-messages (background at pkg-message). A problem which has been vexing me for about 2 months was the lack of results on prod, but results which worked on dev. The issue was raised on GitHub. Initially, it was DDL related (adding a column to the ports table fixed that on test and stage). However, the problem persisted on prod.

Here is an example, which appears later when we get deeper into it:

SELECT DISTINCT id as port_id
  FROM ports
 WHERE pkgmessage_textsearchable  @@ websearch_to_tsquery($1) OR
       pkgmessage_textsearchable2 @@ websearch_to_tsquery($1)

On prod, nothing. On all other systems, we get a few hundred rows. Why?

The key difference between all these databases: the working databases were self-hosted (PostgreSQL 12.13 on FreeBSD 13.1); the problem database was hosted on RDS in AWS (PostgreSQL 12.11 on something…).

It was because of this difference that I feared RDS didn’t support what I was doing. I was wrong.

The problem was configuration. The problem wasn’t that it didn’t work on prod, but why did it work everywhere else?

I’ll give some background as the fields involved in the search, the queries, and how we fixed it. I learned something new yesterday and that’s what is prompting this blog post, so I don’t forget it.

In short, the column was defined with ‘english‘ as the regconfig, but the search query was using the database default value, which was ‘english‘ on some hosts and ‘simple‘ on the problem host.

If you want to skip to why, read So why did it work and not work?

Background

All three databases should contain the same data. That theory was tested by doing queries via ilike which would find the information in the raw data, when the full text search did not.

I first started using this full text search approach back in March 2021 (it was a similar approach to the problem issue, but not the precise one in question). That search was “which port installs this file” – which works without having the port installed. Perhaps your application is calling for that file and you don’t know what installs it. FreshPorts can find it for you. Details in Replacing a column search with a full text search solution.

After reviewing that post, it seems all of my tsvector solutions may require adjustment.

The table

There are two columns on the ports table, one which splits up path names, and now which does not. This table listing is greatly redacted to keep the information to a minimum.

freshports.devgit=# \d ports
                                                                                    Table "public.ports"
           Column           |           Type           | Collation | Nullable |                                                   Default                                                   
 id                         | integer                  |           | not null | nextval('ports_id_seq'::regclass)
 element_id                 | integer                  |           | not null | 
...
 pkgmessage                 | text                     |           |          | 
...
 pkgmessage_textsearchable  | tsvector                 |           |          | generated always as (to_tsvector('english'::regconfig, pkgmessage)) stored
 pkgmessage_textsearchable2 | tsvector                 |           |          | generated always as (to_tsvector('english'::regconfig, translate(pkgmessage, '/'::text, ' '::text))) stored
Indexes:
...
    "ports_pkgmessage_textsearchable2_idx" gin (pkgmessage_textsearchable2)
    "ports_pkgmessage_textsearchable_idx" gin (pkgmessage_textsearchable)
...

The original data is stored in pkgmessage and searches are preformed over the pkgmessage_textsearchable andpkgmessage_textsearchable2 columns.

The query

The main part of the query looks like this:

SELECT DISTINCT id as port_id
  FROM ports
 WHERE pkgmessage_textsearchable  @@ websearch_to_tsquery($1) OR
       pkgmessage_textsearchable2 @@ websearch_to_tsquery($1)

On the development, test, and staging hosts, we get results like this:

freshports.devgit=# SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('example');
 port_id |                           element_pathname                           
---------+----------------------------------------------------------------------
  100421 | /ports/branches/2022Q1/dns/dnsmasq
  100428 | /ports/branches/2022Q1/emulators/xsystem35
   14686 | /ports/head/sysutils/lmon
... etc

I am searching only over one column here, but that doesn’t affect the problem in question.

Running the same query on production yields no results:

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('example');
 port_id | element_pathname 
---------+------------------
(0 rows)


freshports.org=> 

Why? Let’s check the raw data.

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage ilike '%example%';
 port_id |                           element_pathname                           
---------+----------------------------------------------------------------------
   34126 | /ports/head/security/pond
   74559 | /ports/branches/2015Q3/emulators/linux_base-c6
   60310 | /ports/branches/2020Q4/www/gitlab-ce
   38345 | /ports/head/www/gitlab
... etc

Yes, there are items which match. Granted, that word might not stand on its own. So let’s check with leading and trailing spaces:

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage ilike '% example %';
 port_id |                element_pathname                
---------+------------------------------------------------
   60310 | /ports/branches/2020Q4/www/gitlab-ce
   38345 | /ports/head/www/gitlab
   71388 | /ports/branches/2022Q2/math/saga
   51088 | /ports/branches/2018Q4/emulators/qemu
   64192 | /ports/branches/main/editors/lazarus-qt5
   22159 | /ports/head/devel/boost_build
... etc

So, where to next?

Social media

I posted on social media and I received a few ideas via private messages.

My first idea was reindex table concurrently ports – that didn’t help.

Next, we checked Collate and Ctype settings. Both matched:

prod, has problem


                                     List of databases
      Name      |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
----------------+----------+-----------+-------------+-------------+-----------------------
 freshports.org | postgres | SQL_ASCII | C           | C           | 


dev, no issues:


[pg02 dan ~] % psql -l
                                        List of databases
            Name             |    Owner     | Encoding  | Collate | Ctype |   Access privileges   
-----------------------------+--------------+-----------+---------+-------+-----------------------
 freshports.devgit           | postgres     | SQL_ASCII | C       | C     | 

All the same. Nothing there.

tsvector values

We started looking at tsvector next. Here are the values from both columns.

From Controlling Text Search:

to_tsvector parses a textual document into tokens, reduces the tokens to lexemes, and returns a tsvector which lists the lexemes together with their positions in the document. The document is processed according to the specified or default text search configuration.

The following is the results of invoking to_tsvector on the pkg-message file for security/pond as found above in one of the result sets.

It shows us that ‘example’ is the 27th word in the document. However, notice that it is ‘exampl`, without the trailing ‘e’.

freshports.org=> select pkgmessage_textsearchable from ports where id = 34126;
                                pkgmessage_textsearchable                                                                                                              
----------------------------------------------------------------------------------------------------------
 '/tmp':32 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14
 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmpfs':9,18,24,30,31 
 'use':2 'yes':20
(1 row)


freshports.org=> select pkgmessage_textsearchable2 from ports where id = 34126;
                              pkgmessage_textsearchable2                                                                                                             
---------------------------------------------------------------------------------------------------------
 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14 
 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmp':32 
 'tmpfs':9,18,24,30,31 'use':2 'yes':20
(1 row)


freshports.org=> 


In both columns (the collection of lexemes), we have the same 'exampl'.

Let's look at a database which runs this query with expected results (which have been adjusted to readable without scrolling).


freshports.devgit=# select pkgmessage_textsearchable2 from ports where id = 34126;
                           pkgmessage_textsearchable2                                                                                                             
---------------------------------------------------------------------------------------------------------
 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14
 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmp':32 
 'tmpfs':9,18,24,30,31 'use':2 'yes':20
(1 row)


freshports.devgit=# select pkgmessage_textsearchable from ports where id = 34126;
                             pkgmessage_textsearchable                                                                                                              
----------------------------------------------------------------------------------------------------------
 '/tmp':32 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14
 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmpfs':9,18,24,30,31 'use':2 
 'yes':20
(1 row)


freshports.devgit=# 

So look at that. I see exampl (without the trailing e) – now that’s interesting.

Let’s try searching for the shortened word.

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('exampl');
 port_id |                           element_pathname                           
---------+----------------------------------------------------------------------
   34126 | /ports/head/security/pond
   74559 | /ports/branches/2015Q3/emulators/linux_base-c6
   60310 | /ports/branches/2020Q4/www/gitlab-ce
   38345 | /ports/head/www/gitlab
   46842 | /ports/branches/2018Q1/mail/postfix-sasl
...

Hah! It’s finding that. Good. Progress. And not finished yet. Why does it work on one host, but not the other, given we have the same lexemes.

What’s the default_text_search_config setting on each host?

The configuration parameter default_text_search_config specifies the name of the default configuration, which is the one used by text search functions if an explicit configuration parameter is omitted. It can be set in postgresql.conf, or set for an individual session using the SET command. (taken directly from that documentation URL).

Also from the docs: “A text search configuration specifies all options necessary to transform a document into a tsvector: the parser to use to break text into tokens, and the dictionaries to use to transform each token into a lexeme.”

What is that setting on my hosts? On the problem host:

freshports.org=> show  default_text_search_config ;
 default_text_search_config 
----------------------------
 pg_catalog.simple

On the working hosts:

freshports.devgit=# show  default_text_search_config;
 default_text_search_config 
----------------------------
 pg_catalog.english
(1 row)

freshports.devgit=# 

There is a difference, and this turns out to be the cause of the problem.

However, as pointed out by Tom Lane’s reply to my post on the PostgreSQL General mailing list, that setting is not going to play well with my column definitions, both of with use this:

...to_tsvector('english'::regconfig...

Because ‘because “english” and “simple” will stem words differently.’ – OK, let’s look.

In short, the

Example tests

Here are the tests Tom provided:

regression=# select websearch_to_tsquery('english', 'example');
 websearch_to_tsquery 
----------------------
 'exampl'
(1 row)


regression=# select websearch_to_tsquery('simple', 'example');
 websearch_to_tsquery 
----------------------
 'example'
(1 row)

From that, it is clear that I should be using simple, not english.

Where are my settings set?

Settings can come from a configuration item or from the default value for the system.

The problem host:

freshports.org=> select setting, source from pg_settings where name = 'default_text_search_config';
      setting      | source  
-------------------+---------
 pg_catalog.simple | default
(1 row)

The working hosts:

freshports.devgit=# select setting, source from pg_settings where name = 'default_text_search_config';
      setting       |       source       
--------------------+--------------------
 pg_catalog.english | configuration file
(1 row)

Proof of concept

Let’s create a new column and index, in prod, and see how that goes.

Here we go, on the problem database, create a new field, based on simple, not english.

ALTER TABLE public.ports
    ADD COLUMN pkgmessage_textsearchable3 tsvector generated 
     always as (to_tsvector('simple'::regconfig, translate(pkgmessage, '/'::text, ' '::text))) stored;

Index it:

CREATE INDEX ports_pkgmessage_textsearchable3_idx
    ON public.ports USING gin
    (pkgmessage_textsearchable3)
    TABLESPACE pg_default;
CREATE INDEX

Try the problem query:

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable3  @@ websearch_to_tsquery('example');
 port_id |                           element_pathname                           
---------+----------------------------------------------------------------------
   34126 | /ports/head/security/pond
   74559 | /ports/branches/2015Q3/emulators/linux_base-c6
   60310 | /ports/branches/2020Q4/www/gitlab-ce
   38345 | /ports/head/www/gitlab
   46842 | /ports/branches/2018Q1/mail/postfix-sasl
   51532 | /ports/branches/2019Q1/graphics/drm-legacy-kmod
... etc

Success. Thank you Mr Lane.

Looking at the values

Let’s have a close look at the tsvector values (I have wrapped the output for readability):

freshports.org=> select pkgmessage_textsearchable3 from ports where id = 34126;
                                                                                                                                                            pkgmessage_textsearchable3                                                                                                                                                            
-------------------------------------------------------------------------------
 '1':10 '2':21 '3':33 '5':17 'a':5,23 'and':37 'as':4 'client':6,36 'directory':25 
 'example':27 'follow':38 'following':13 'for':26 'in':15 'instructions':40 'line':14 
 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'require':8 'run':34 
 't':29 'the':12,39 'tmp':32 'tmpfs':9,18,24,30,31 'to':1 'use':2 'yes':20 'you':7
(1 row)

So why did it work and not work?

On both hosts, the column was being indexed using ‘english’, which converted ‘example’ to the value ‘exampl’.

In my query, my search parameter was also being converted, using the configuration defined on that host.

In production, the search criteria would be converted like this:

freshports.org=> select websearch_to_tsquery('example');
 websearch_to_tsquery 
----------------------
 'example'
(1 row)

That is, no change, and no match to the tsvector lexemes.

On the working hosts, this happened:

freshports.devgit=# select websearch_to_tsquery('example');
 websearch_to_tsquery 
----------------------
 'exampl'
(1 row)

So, the same converted value, which matches the tsvector lexemes which means the search succeeds.

In short, the column was defined as ‘english’ and we were searching using ‘simple’.

Fixing the original query

I could fix the original query by supplying regconfig when invoking to_tsvector. Here is the original problem query, slightly altered:

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('english', 'example');
 port_id |                           element_pathname                           
---------+----------------------------------------------------------------------
   34126 | /ports/head/security/pond
   74559 | /ports/branches/2015Q3/emulators/linux_base-c6
   60310 | /ports/branches/2020Q4/www/gitlab-ce
   38345 | /ports/head/www/gitlab
   46842 | /ports/branches/2018Q1/mail/postfix-sasl
   56061 | /ports/head/games/homura
   51532 | /ports/branches/2019Q1/graphics/drm-legacy-kmod
...etc

The original query used websearch_to_tsquery(‘example’);. That is why it didn’t work on prod. Because the default regconfig (‘simple’) didn’t match the column (‘english'(.

This query worked on the other hosts, because the default regconfig (‘english’) matched the column (‘english’);

Why store the tsvector values?

I was asked: why do you have tsvector columns in the table? As opposed to just having an index over a tsvector of the actual text column.

I think it was a progression thing. I started following a tutorial or something.

Instead of having an index over the column pkgmessage_textsearchable, as I have now, I could create an index such as to_tsvector(‘english’::regconfig, pkgmessage).

Perhaps that is for future work.

Edit 2022-12-19 – I tried a proof-of-concept. It failed. Initially. Then I added a new index.

See https://explain.depesz.com/s/eb6t – I’ll add more details here as I complete this task later.

edit 2022-12-23 – the index conversion was documented in Moving from storing the tsvector values to a tsvector index.

What’s next

Next, I have to decide if I want to modify my queries to explicitly specify regconfig or rely upon the configuration setting. What are the pros and cons of each? Please let me know your thoughts via the comments.

Dec 162022
 

Tonight I was setting up a new FreshPorts node for development. It took me a while to remember the steps.

sudo su -l postgres
psql template1 < globals.sql
createdb  -T template0 -E SQL_ASCII freshports.org
psql freshports.org

create language plperl;
exit;
time pg_restore -j 32 -d freshports.org ~dan/dump/freshports.org.dump
Sep 202022
 

Based on the Add links to GitHub and GitLab mirrors for ports and commits request, the website has some new links arriving soon.

  • Link to GitHub from each commit
  • A similar link to GitLab

Use the one you like. Each git web interface has it’s own followers.

Also, while there, the commit hash at the top of the page is now a link to the commit history further down the page.

EDIT: It seems that link has been present since FreshPorts started processing git commits, and it has always been a link to that commit within cgit.freebsd.org. What changed was the version at the top of the page. That is now a link to #history.