I went to work on a brilliant new idea for a FreshPorts feature and I immediately hit a snag on my dev system. I noticed that the package importing system had not run anything recently. The reason why was in the logs: In this post: FreeBSD 14.0 PostgresQL 16.1 py39-psycopg2-2.9.9 Python 3.9.18 First, why did […]
could not open certificate file “/root/.postgresql/postgresql.crt”: Permission denied Read More »
Yesterday, an incident with a commit wound up taking up a lot of time to process on FreshPorts. The purpose of this post includes: document how the commit was handled on different nodes document the fixes for some nodes investigate how the code can be improved to detect such situations and abort See also How
When a commit goes wrong Read More »
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:
Fixing permissions on the packages_raw table Read More »
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
Moving from storing the tsvector values to a tsvector index Read More »
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 »
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 This takes about 45 minutes.
note to self: restoring a database 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
missing ports_categories entries Read More »
Following on from yesterdays Quarterly branches: what’s next? and Processing commits on branches with git, I’ve started in on clearing commits from cache. The code First step, create a table which looks like this: freshports.devgit=# \d cache_clearing_commits Table “public.cache_clearing_commits” Column | Type | Collation | Nullable | Default —————–+—————————–+———–+———-+——— commit_to_clear | text | | not
Commit cache clearing Read More »
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 »