This post is another in the packages series. It documents how the data is transformed from the raw form (packages_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 »
This is the latest in a series of posts dealing with displaying what packages are available for a given FreeBSD port. The Packages – how is this data stored? post may help you follow along. It outlines the data and the tables used in this post. Last night I managed to get the right data
Procedural vs query approaches Read More »
When I started this, I thought it was be straight forward and take only a few hours at most. It turned into multiple hours over two days. This is part two of a two-part post. The first part dealt with changing a database column from time to timestamp. In this post: PostgreSQL 12.2 Nagios 3.5
creating a Nagios check on a PostgreSQL table Read More »
The plan for today was not decided until after I got out of bed. I sat down with coffee and looked through the list of tasks. I found one which appealed: monitor the cache_clearing_ports & cache_clearing_dates tables This is part one of a two-part story. The first part deals with fixing a database table before
Changing a column type Read More »
FreshPorts had a cache clearing issue recently. It lingered. For a long time. It took me a while to figure it out. It turned out to be a Python 3 compatibility issue. In this post: PostgreSQL 12.1 FreeBSD 12.1 I will outline how database changes invoke external scripts asynchronously which then clear the cache. I
Cache clearing issue Read More »
Yesterday I copied data from the old production server to the new production server. One thing I missed, but did think about at the time, was updating the sequence used by the table in question. Looking at the table definition: The report_log_id_seq value will be wrong. When the reports run, they will use values for
Fixing a sequence value Read More »
Ever since the new server went into production, sometime in 2017, the notification emails, which tell you what has changed, have been coming from the old server. I never did that changeover. The old server was still processing incoming commits, so the notifications were accurate. However, new accounts would not get notifications and changes to
Moving the email notifications to the new server Read More »
I originally documented this so I would have a reference while tracking down the problem. I have since solved the issue and you can jump to the bottom of this post to find the solution. What I do not know is why this was not an issue under PostgreSQL 10.6 but was under PostgreSQL 11.1.
procedure OK under 10.6 but starts acting up under 11.1 Read More »
This post serves two goals: document the CONFLICTS implementation within FreshPorts show how you can use triggers to update a secondary table The FreeBSD ports tree / package collection has a method for resolving conflicts. This feature allows a port maintainer to specify what, if any, packages conflict with a given port. The reasons why
Adding and populating CONFLICTS Read More »
Databases use relational integrity to enforce expected situations. A common scenario is duplicates. Case in point, I present the port_dependencies table: For those not familiar with FreeBSD ports, each port (you could also refer to them as a package or application) can have zero or more dependencies. The FreshPorts database extracts and lists these dependencies
Duplicate dependency issues – avoiding duplicate rows Read More »