PostgreSQL

Converting from int to bigint and from serial to identity

Yesterday, a Facebook memory reminded me of an old issue with FreshPorts: serial values which are about to overflow. This is where you may be thinking? serial? In this day? You might also think, why serial? Why not UUID. Keep thinking about that. Some of the tables we’re talking about were designed 20+ years ago.

Converting from int to bigint and from serial to identity Read More »

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

Update, 9 November 2026: I have an answer to the question at the end of this post. libpq was looking in /root because HOME was still /root: su -m keeps the caller’s environment. sslcertmode=disable gets past the certificate, but not the .pgpass lookup, which goes to the wrong place for the same reason. The details

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 »

Scroll to Top