PostgreSQL Extensions with Nix and devenv

Today, at $CURRENT_JOB, we began working on an application that leverages a PostgreSQL extension called periods. According to its README:

This extension recreates the behavior defined in SQL:2016 (originally in SQL:2011) around periods and tables with SYSTEM VERSIONING.

Fortunately, Nix and devenv do all the hard work for us. In order to have a PostgreSQL 15 package with the desired extensions, one can do the following with Nix:

pkgs.postgresql_15.withPackages (p: [ p.periods ]);

Well, this expression only results in a PostgreSQL package with the periods extensions available. However, it doesn’t do anything other than that, not even installs it on our machine. Devenv solves this issue for us, letting us declaratively configure a PostgreSQL instance for local development:

# omitted code for brevity
services.postgres = {
  package = pkgs.postgresql_15.withPackages (p: [ p.periods ]);
  enable = true;
  port = 5432;
  listen_addresses = "";

Running the command nix develop --impure followed by devenv up will spin up a local PostgreSQL instance. Let’s check the extensions we have:

$ psql
db=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

Where’s the periods extension? Well, it is available, not installed!

db=# select * from pg_available_extensions where name = 'periods';
  name   | default_version | installed_version |                               comment
 periods | 1.2             |                   | Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
(1 row)

Installing it “by default” is as easy as adding this new attribute on devenv:

services.postgres = {
  # ...
  initialScript = ''
    CREATE EXTENSION btree_gist;

Articles from blogs I follow around the net

The four tenets of SOA revisited

Twenty years after. In the January 2004 issue of MSDN Magazine you can find an article by Don Box titled A Guide to Developing and Running Connected Systems with Indigo. Buried within the (now dated) discussion of the technology…

via ploeh blog March 4, 2024

Building a demo of the Bleichenbacher RSA attack in Rust

Recently while reading Real-World Cryptography, I got nerd sniped1 by the mention of Bleichenbacher's attack on RSA. This is cool, how does it work? I had to understand, and to understand something, I usually have to build it. Well, friends, that is what…

via blog March 4, 2024

How to unbreak Dolphin on SteamOS after the QT6 update

A recent update to Dolphin made it switch to QT6. This makes it crash with this error or something like it: dolphin-emu: symbol lookup error: dolphin-emu: undefined symbol: _Zls6QDebugRK11QDockWidget, version Qt_6 This is fix…

via Xe Iaso's blog March 3, 2024

Generated by openring