Thought I'd follow up on a previous post regarding recursive queries in PostgreSQL. I had opportunity to use this particular query again, and I thought I'd share a couple small enhancements:

CREATE FUNCTION subaccounts(INTEGER) RETURNS SETOF accounts AS $$

DECLARE
  row accounts%rowtype;

BEGIN
  FOR row IN (
    WITH RECURSIVE subs(id) AS (
      SELECT * FROM accounts WHERE id = $1
    UNION ALL
      SELECT a.* FROM accounts a, subs
      WHERE a.accounts_id = subs.id
    )
    SELECT *
    FROM subs
  ) LOOP RETURN NEXT row;
  END LOOP;
END;

$$ LANGUAGE 'plpgsql';

The key features here are

1. Refactored into a function, so you can pass it an id. This allows you to select any branch from the tree.
2. Included the parent record in the result set. This turns out to be more useful to me.

Here's an example usage scenario:

SELECT * FROM accounts;

 id | accounts_id |   name
----+-------------+-----------
  1 |             | Account 1
  2 |             | Account 2
  3 |           1 | Account 3
  4 |           3 | Account 4
(4 rows)

SELECT * FROM subaccounts(1);

 id | accounts_id |   name
----+-------------+-----------
  1 |             | Account 1
  3 |           1 | Account 3
(2 rows)

from "man tmpwatch":

Make the decision about deleting a file based
on the file's atime (access time). This is the default.

Combine this with a common trick of turning atime off on filesystems and you have a recipe for breaking things that expect their temporary files and directories to be around when they need them. I configure Nginx to put its temp files under /var/tmp/nginx, but that directory kept disappearing. I added code to build the directory tree when Nginx is started, but they would disappear while it was running. Turns out that noatime is the culprit, and tmpwatch is the unwitting accomplice.

There's a few ways to fix this issue:

1. Put your temp files somewhere else.
2. Use relatime rather than noatime. Unfortunately relatime is only available in newer kernels (2.6.18 doesn't have it).
3. Change tmpwatch to use --ctime. There's a nice writeup on this here.

In the case of Nginx, I actually decided to move the temp files to /var/run/nginx. Since the files in question are actually temporary buffers that Nginx manages on its own, this seemed the right solution.

I'm not a big fan of screen or tmux because they have a lot of features I don't use and get in the way some things I actually do use (namely requiring bizarre key chords for scrollback and copy/paste). Instead I prefer dtach.

However, dtach requires passing too many arguments for my taste and level of laziness. Here's my shell function (placed in ~/.bashrc) for simplifying my dtach session management. It's nothing special but just one of those little things that makes daily life a bit simpler:

dtach () {
   case "$1" in
       -ls)
           # ls -1A ~/.dtach/
           ls -lrt1A ~/.dtach/ | awk '{print $9, "--", $6, $7, $8}'
           ;;

       *)
           name=$*
           if [ "$name" == "" ]; then name="default"; fi
           mkdir -p ~/.dtach/
           /usr/bin/dtach -A ~/.dtach/"$name" -z /bin/bash
           ;;
   esac
}

Now I can arbitrarily name my sessions, "dtach -ls" shows me a list of sessions, and plain old "dtach" starts or attaches to the "default" session.

I saw the disclaimer starting with "Dear visitors from Google..." about halfway down this page and my brain refused to process what the disclaimer appeared to be telling me. People couldn't really be this stupid...

Then I read the comments.

I have tears in my eyes, but I don't know if it's from laughing or because the world is full of idiots. And they have internet access.

I just want to thank all the inconsiderate shitheads who remembered that I have been under a rock, surely without Internet, for the past decade and THANK GOD, remembered me and sent me a fucking reminder that I'm missing out on LinkedIn where I can... well, I don't know, do some shit or perhaps meet other dumb-asses like yourself.

Seriously people. Everyone and their grandmother is fully aware of Facebook, LinkedIn, MySpace etc. There is no fucking reason on earth to send another fucking reminder to the people unfortunate enough to be your friends. If they aren't on there by now, chances are it's because they don't want to be, and there's at least a slight chance that they weren't just waiting for that special invitation from your goofy ass.