PostgreSQL

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 »

lack of index brings website to a crawl

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: …

lack of index brings website to a crawl 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 »

Replacing a column search with a full text search solution

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 »

Changing how we record ports which need cache clearing

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 »

ignore case when browsing to a cat/port – the details of how to do that

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 »

Using DEFERRABLE INITIALLY DEFERRED on constraints

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 »

Scroll to Top