With UPDATE/DELETE FOR PORTION OF looking like it will land in Postgres 19, I’ve been thinking about next steps . I read a very helpful paper on temporal relational algebra last May by Richard Snodgrass. Here are some notes on it. The paper was “An Overview of TQuel”. It’s Chapter 6 in Temporal Databases: Theory, Design, and Implementation from 1993. TQuel was an extension to Quel, the query…
I seem to have an unusual way of running Claude Code, but I find it a good trade-off between convenience and security. I was explaining it on HN , but I wanted to write it down here too: I always run with --dangerously-skip-permissions (or whatever it’s called; there’s a global flag I set a long time ago). I assume everyone does. It’s too tedious otherwise. But how can I do that with tolerable…
I got a GeForce RTX 5070 Ti GPU for Christmas. I want to do some AI stuff with it. Maybe I’ll have something about that to post soon. But getting it to work on my system was really a chore! I’m running Debian 13 with a ROG STRIX B550-F motherboard (not the wifi version). After I put it in and tried to boot, I saw Grub’s selection screen, but almost immediately my monitor went black, and within 10…
I had to fix some shift/reduce conflicts in the Postgres bison grammar recently. I’ve never done this before, so it was a learning experience. Maybe my story will help some other new Postgres contributor—or anyone struggling with this technology that is central to computer science but for many day-to-day programmers seldom-used. Back in 2018 I read lex & yacc by Doug Brown, John R. Levine, and…
Both logical decoding and logical replication use a table’s REPLICA IDENTITY . This is a way to say which row was changed by an UPDATE or DELETE . In other word it identifies the “old” row. Logical decoding will use the replica identity to say which row was changed, if available, but if not then the information is simply omitted. No big deal. In logical replication , the subscriber looks for the…
Logical decoding is different from logical replication . Logical replication is built on logical decoding. Logical Decoding Logical decoding exports the changes happening in your database. They are streamed through a replication slot . The slot keeps track of how far you’ve read, so that it doesn’t skip or repeat messages. (Btw how is it not “at least once” delivery?) Basically you are getting a…
Way back in Februrary Peter Eisentraut asked me if I’d tested the performance of my patch to add temporal foreign keys to Postgres . Have you checked that the generated queries can use indexes and have suitable performance? Do you have example execution plans maybe? Here is a report on the tests I made. I gave a talk about this last month at pdxpug , but this blog post will be easier to access,…
Benchbase is a framework from Carnegie Mellon for benchmarking databases. It comes with support for about 20 benchmarks and about as many DBMSes. Benchbase started life as OLTPBench as was introduced in an academic paper from 2014. Using Benchbase the last month, I found the documentation to be pretty shallow, so this is my effort to improve things. A lot of this material was covered in my pdxpug…
Last Thursday I gave a talk at PDXPUG about using Benchbase to compare the performance of temporal foreign keys . It was a lot of fun, and a really good turnout. There were even folks from Seattle and Bend. After listening for an hour, people stuck around and talked about databases and benchmarks for another two, then the last few holdouts went out for drinks for another hour and a half. At least…
One silver lining of temporal primary & foreign keys getting reverted is I got to meet Hettie Dombrovskaya and Boris Novikov . I’ve been working with them to write SQL for various temporal operations not covered by the SQL:2011 standard. There is no support there for outer joins, semijoins, antijoins, aggregates, or set operations ( UNION , INTERSECT , EXCEPT ). As far as I know no one has ever…
Saturday I debugged the sprinklers. I thought I turned them on two weeks ago, and I heard someone’s sprinklers outside my window that next Monday morning at 5 a.m., but after a week of 100-degree days it was clear ours weren’t doing their job. I had skipped my usual routine of checking each line, unearthing the sunken heads, and replacing what had failed. So now I had to deal with it. Somehow…
My work adding temporal primary keys and foreign keys to Postgres was reverted from v17 . The problem is empty ranges (and multiranges). An empty range doesn’t overlap anything, including another empty range. So 'empty' && 'empty' is false. But temporal PKs are essentially an exclusion constraint using (id WITH =, valid_at WITH &&) . Therefore you can insert duplicates, as long as the range is…
Here on illuminatedcomputing.com I’ve got a bunch of sites served by nginx, but I’d like to run a little k3s cluster as well. The main benefit would be isolation. That is always helpful, but it especially matters for staging sites for some customers who don’t update very often. Instead of migrating everything all at once, I want to keep my host nginx but let it reverse proxy to k3s for sites…
The kids and I love to play games on the weekend. Our My favorite is Agricola, a board game about farming in the Middle Ages. We also play a lot of matching games. Saturday morning Elsa invented her own matching game using the cards from Abandon All Artichokes. Then I made up a matching game too. I called it “Cozy Toes”. It was a money game. Whoever got the most pairs of matching socks won, and…
UPDATE: My temporal patches were reverted from v17 . Hopefully they will be accepted for v18 instead. Today first thing in the morning I saw that the first part of my temporal tables work for Postgres got merged . It was two patches actually: a little one to add a new GiST support function and then the main patch adding support for temporal primary keys and unique constraints based on range types.…
In Postgres development it’s normal for patch attempts to require many revisions and last a long time. I just sent in v17 of my SQL:2011 application time patch . The commitfest entry dates back to summer of 2021, but it’s really a continuation of this thread from 2018 . And it’s not yet done. My work on multiranges is a similar story: 1.5 years from first patch to committed. Today I saw this post…
When it comes to sending email in Rails, I’ve wondered for years about the gap between this: class UserMailer < ApplicationMailer def welcome (user) @user = user mail( to : user.email) end end and this: class User def send_welcome_notification UserMailer .welcome( self ).deliver_later end end We are defining an instance method , but we are calling a class method . What’s going on there? I finally…
Ubuntu has a very nice way of organizing multiple versions of Postgres. They all get their own directories, and the commands dispatch to the latest version or something else if you set the PGCLUSTER envvar or give a --cluster option. For instance if you have installed Postgres 14, you will see files in /usr/lib/postgresql/14 and /usr/share/postgresql/14 . In Postgres a single installation is…
Rails has lots of methods to see what attributes have changed on your model. Some tell you the changes you haven’t yet saved; some, the changes you just saved. But the behavior and names of these attributes have changed over time . I thought I had a handle on this until I saw saved_change_to_attribute? and wondered how it differs from attribute_previously_changed? . Turns out they are identical!…
Introduction This blog post is a survey of SQL:2011 Temporal features from MariaDB, IBM DB2, Oracle, and MS SQL Server. I’m working on adding temporal features to Postgres, so I wanted to see how other systems interpret the standard. If you’re new to temporal databases, you also might enjoy this talk I gave at PGCon 2019. In this post I cover both application-time (aka valid-time) and system-time,…