Wandering Thoughts archives

An obvious realization about SQL foreign keys

In the process of writing the last entry, I finally realized something about SQL foreign keys. Specifically, about what you could call their lifetime, by analogy to object lifetimes in garbage collected languages.

Everyone knows (hopefully) that you don't want to have long-lived objects hold references to what should be short-lived objects. In a GC'd environment such (strong) references convert the short-lived objects into long-lived ones; they can't be GC'd before the long-lived object drops its reference to them, and that's normally not going to happen until the long-lived object itself is deleted. Poof, some of your short-term objects are now long-term.

In light of the issue with deleting records from the last entry, it's clear that foreign key relationships need to have a similar respect for entry lifetimes. A table for long-lived entries should not have an explicit foreign key to a table for short-lived ones. Instead, you can only safely have foreign key pointers to tables where the data is at least as long-lived as entries in your table.

(In SQL the problems can implicitly run the other way; rather than your short-lived objects being long-lived, your long-lived objects suddenly become short-lived as they are cascade-deleted for you.)

This is especially silly problem to give yourself in SQL. Unlike with objects, in SQL you don't need an explicit foreign key relationship in order to link two tables together. You can trivially construct the SQL equivalent of weak references by just not specifying your field as an explicit foreign key; you still join on it as usual. In ORMs you will generally have to do a little bit more work, but it's not crushingly more.

In retrospect this makes using any explicit foreign keys in my audit table schema an obviously bad idea. Audit table entries are likely to be the longest-lived objects in my database; making them depend on short-lived things like account requests is about as classical an entity lifetime mistake as I could have made. And it's not as if I need to query through the foreign key relationship very often; in fact, not at all in the actual code so far. (As far as the code is concerned, audit entries are write-only.)

I'll definitely have to bear this in mind for any future database schemas that I design.

On programming (and me)

Every time I wind up doing a substantially amount of coding, I end up rediscovering how much I enjoy programming and how rewarding it feels. It's more than just the monkey rewards from doing things and getting feedback, it's all sorts of pleasure rolled up in a big complicated ball that I just love without being able to describe why I find it so absorbing. I just like programming, even if I forget how much I do when I haven't done it for a while.

(Sometimes I find myself half regretting that I became a sysadmin instead of staying a programmer. Programming as a career can be terribly frustrating and undoubtedly soul-destroying, which is kind of why I wound up not in it. But every so often I miss the grass on the other side of the fence and think of hopping back over, however infeasible it is by now.)

Partly it's the pleasure of solving puzzles that matter, and partly it's the pleasure of building and refining things, of seeing what I imagined take shape in front of me by the dint of my own effort and cleverness. But none of that is all of it. Perhaps part of it is the stress relief of shutting everything else out and focusing all of my thinking on the programming instead of other stress points; it's certainly possible that this is a factor in my latest enthusiasm. Certainly I resent having to take time away from coding right now, just when I'm in the flow and I'm so close to getting everything done. (I'm not, but programmers are great at self-delusion.)

(Unlike a lot of programmers, I've never found puzzles interesting on their own; when I look at something like Sudoku, my mind soon works out that I'm not really getting anything for my effort and immediately loses interest. I suspect that this is related to my problem with learning new programming languages.)

Invariably once I've dug my way sufficiently far into the project, I don't want to do anything else but code and the project takes over my mind. I wind up resenting meetings, other work, and even outside activities because they're all taking away valuable time that I could use to be heads down in my editor. Even when I want to step away from the code it's awfully hard; there's always one more quick thing that I see how to do, one more idea to write a quick note about and then a quick implementation and then it's half an hour later. Maybe 45 minutes. Maybe more.

It's probably good for my sanity that I've left my editor running at work with various files loaded into buffers, so that I can't do just one quick change from home even if I wanted to. (Because one quick change is never either.)

(As as side note, I once more or less boggled some people I knew by telling them that I was using recreational programming as a form of stress relief. I don't know if this makes me unusual as far as programmers go; from the stereotypes, I'd suspect not.)

Poison pills, a tale of interrupts versus highly structured systems

Once upon a time way back when, I was briefly involved with a research operating system that was written in a highly structured, layered, and modular manner with strong isolation between all of the components, in a strongly typed and scoped language (ie, all of the best practices of mid to late 1980s academic software engineering). It was also intended to be POSIX compatible, and this created a little problem.

Most of the time, Unix-like terminal handling is very nicely modular and layered. You can easily design a stack of modules that process terminal input from the raw hardware on up through terminal processing and the eventual read() system call returning; the driver returns raw characters, the processing layer handles basic line editing and accumulates the result into lines, and so on. Then some joker hits control-C.

Control-C is an extremely awkward thing in a highly modular and layered system. First, it's an asynchronous activity with immediate effect, and second its effects have (almost) nothing to do with terminal input. In a structured modular system, the terminal input handling code simply does not have direct access to the process handling code that sends signals; that access exists only at a much higher level in the overall kernel structure.

(Control-C is not the only input character with such non-local effects, but it is the one that you absolutely cannot get away with ignoring if you want to have an even vaguely usable system.)

To get around this, the system implemented control-C handling with a special hack that I heard called 'poison pills'. Normally the terminal system's 'read' interface returned characters, but it could also return special magic results to signal various special events, ie poison pills (so called presumably because they generally killed the process involved in consuming them). These poison pills propagated up through the various levels of code, and once a poison pill made it up to the high level that had access to the process interface, it was 'eaten' and the process signaled or killed outright.

(Much of this was carefully wrapped up in various opaque types and module interfaces.)

While I sort of admired the poison pill hack, the major lesson that I took away from this experience was that excessively strict modularity and isolation was a bad thing. Real systems do not nicely break down into neat hierarchies of functions and modules because there are connections that cut across your clean boundaries. While you can handle these 'correctly' inside your design with enough work, the contortions required to do this are generally worse than those required to deal with the cross connections.

(The other lesson I took away was that operating systems should not be written in highly structured and controlled languages.)