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, …
When tsvector was working as expected on most hosts, but not one Read More »
A long-standing bug, perhaps dating back to April 2003, has been fixed. It was an optimization in a trigger: if the values in the trigger have not changed, let’s not run the trigger. Turns out, that was a bad idea. The table The ports_categories table documents the connections between a port and the categories in …
Today at about 1:00 pm Philadelphia time, the website went offline. It was still there, it just could not answer any queries. In this post: Website and database server running in AWS FreeBSD 12.2 on a t3.medium EC2 instance PostgreSQL 12.5 running on a db.t2.large RDS instance Initial look Looking at load, it was acceptable: …
I had noticed a problem with my full text search solution when I was testing. If the input query contained a quote, an error resulted on the query. At the time, I attributed that to a poor input/text handling. Later, I realized I was wrong. On the website, this PHP code is used: Where $this->Query …
to_tsquery gives ‘ERROR: syntax error in tsquery’ when it contains a space Read More »
In yesterdays blog post, Replacing a column search with a full text search solution, I wanted to mention how adding a LIMIT 10 to a fast running query made it run slower. I didn’t remember that issue until this morning. Fortunately, the fix was low cost. The query This is the query I ran: The …
A PostgreSQL query runs in 1.7s – add a LIMIT 10, runs in 28.2 seconds Read More »
One of the many useful features on FreshPorts is: what port[s] install[s] this file? That’s the pkg-plist search option. pkg-plist is a file which “lists all the files installed by the port”. However not all ports have a pkg-plist file because the list is compiled automatically. That is why the configure_plist table was created to …
Replacing a column search with a full text search solution Read More »
In this post I will show you how I discovered that the backend was putting data into a new table (generate_plist) but the website was still using the ports.pkg_plist column. The result was stale data being shown on the website (for older ports) or no data at all (for newer ports). How long? Since Oct …
Converting a column-based expression to a row based solution Read More »
In this post I’ll talk about how the code intially took the easy way out and how recent changes have tightened that up. In short, the code would raise N messages when just a single notification is sufficient. Today the full vuxml database was reloaded into FreshPorts. This affects approximately 3454 ports and the marking …
Changing how we record ports which need cache clearing Read More »
This idea came to me today from swills: stupid question, would it be possible to make the urls on freshports case insensitive? so that like https://www.freshports.org/x11/libx11 would go to https://www.freshports.org/x11/libX11 After confirming this was for manually typed URLs (as opposed to generated links), I started looking into it. This is what I found. In this …
ignore case when browsing to a cat/port – the details of how to do that Read More »
This post is another in the packages series. It documents how the data is transformed from the raw form (pacakges_raw) into normalized data (packages). The Packages – how is this data stored? post may be useful reading. The primary purpose of this blog post is documentation of the above mentioned transformation process. Along the way …
Using DEFERRABLE INITIALLY DEFERRED on constraints Read More »