PostgreSQL

could not open certificate file “/root/.postgresql/postgresql.crt”: Permission denied

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 »

Moving from storing the tsvector values to a tsvector index

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 »

When tsvector was working as expected on most hosts, but not one

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 »

Commit cache clearing

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 »

to_tsquery gives ‘ERROR: syntax error in tsquery’ when it contains a space

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 »

A PostgreSQL query runs in 1.7s – add a LIMIT 10, runs in 28.2 seconds

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 »

Scroll to Top