Struggling against the almighty tides of eventuality

I went to the “Spaghetti Disco” at the church up the street from me tonight. It’s a benefit for a library-ish community center, also up the street from me (though going the other direction). All in all, it wasn’t bad. It led me to a realization, though, or rather led me to realize something again:

I’m a bit rusty.

There are a number of things at which I’m rusty: playing music, playing Quake (not that I was ever particularly good), and playing the social scene. Alright, I’ll admit that last one didn’t work. I’m a sucker for trying to stretch the rule of threes. But the point is, I’m just not very good at socializing. It’s probably not a big shock, and in fact you could likely discern this from not-subtle contextual clues here on this very blog. But there is positivity in that I’m trying, I think.

I think the main thing I still have to get over is wondering whether or not people like me. From what I can understand of people who are successful at having friends, they don’t spend a lot of time thinking about it. It may cross their mind from time to time, but it’s not the world-ending calamitous spiral into despair that it winds up being for me. Of course, that may be coming more from the fact that I’m a compulsive worrier than that I’m particularly bad at human interaction.

All things considered, though, I shouldn’t have anything to complain about with tonight. The event itself was pretty well organized, and definitely well attended. I finally met my foursquare nemesis, and he and his fiancée were cool people. Also, I found a beer which I don’t mind drinking (a rare breed indeed): Dundee Honey Brown. I wound up jetting after the raffle and the prizes, as the one beer I had didn’t get me loosened up enough to “shake [my] booty,” and as the bar was cash only I could not procure another. The building feeling that I was sticking out didn’t necessarily help, either.

But, like I always say at the end of these self-pitying blog posts, I must simply try to do better. It’s easy to complain about my failings, and probably necessarily cathartic in a way, but simply wallowing in it will not do. I’m not sure if I come off weird, or stupid, or any number of other negative adjectives. Most people probably wouldn’t tell me anyway, like a piece of food stuck on my chin. People are prone to such harmful niceties. But I can’t really let that be a defining issue for me. The only way to get better at performing is to perform more often. Being rusty shouldn’t be an excuse anymore.

This is mostly in response to this post by another Baltimore native, in addition to some conversation I’ve had with him since. The feelings I have simply could no longer be expressed in 140 characters or less, and I’m not a fan of the long-form multi-tweet thought.

I realize that Baltimore is not a city in the north or in the midwest; 3 feet of snow is not something that happens every winter. After all, we tend to get a nice “wintry mix” of about 3 inches of wet snow that melts the next day and it sends most people into a blind panic. Given that the entire city hasn’t collapsed into a swirling vortex of despair, I’d say we’re doing pretty well. But that doesn’t mean I don’t think we can do better.

Given that we got hit with two storms in quick succession, a good two feet followed a few days later by another foot or so, I realize that there’s much to be done. Streets that were formerly plowed now need to be gone over again, and in the middle of a city there’s not much place for all that snow to go. However, my street was visited by a plow about an hour or two before the first flakes of second storm. That’s fine, it helps keep the total amount to be plowed next time down. Of course, that’s assuming they actually plowed. After sitting at the end of my block for a good few minutes (I’d assume they were digging out, except they’d have to have been doing it by telekinesis), they trundled down our street, plowing about halfway before lifting the scoop up and continuing on out of our block.

It’s not a terrible thing to take a break, as I’m sure those guys had probably been driving around city streets for many hours. But, if you’re actually going to resume plowing, I’d appreciate it if you plowed the whole street, and not just a little bit. Sure, it’s a business at the end of the block and not houses, but we still need to drive there. Since that time, like much the rest of the street, there is now a nice patch of packed-in snow sitting on top of a sheet of ice. Very nice.

Again, they have a whole metric shitload of snow to move all around the city, and it’s not unreasonable to assume that a silly residential side-street will get less attention than the main thoroughfares. But I actually ventured out into the city in my car for the first time since Thursday today, and I was delayed a good few minutes by a snow removal crew. The street was fine; they were clearing parking spaces. For that, I wouldn’t fault them, because I know people have been complaining about parking spaces, snow removal routes, etc. However, they weren’t in front of houses, or even a heavy business district. They were clearing spots in front of a church, and a garage (access to the garage was already available). At this point, I’d say that maybe their time would be better spent clearing out the side streets.

I say that because, after eventually getting back to our house, I had to grab a shovel and spend 20 minutes trying to break up the ice in the center of the road so that we could have enough traction to actually park in our carefully-cleaned spot. As an aside, here’s a note to other Baltimore city-dwellers: we did not put a chair in our spot; that crap is tacky. We got lucky and got our spot back but were willing to grab a shovel and make another one if need be. We shoveled a patch of pavement; doesn’t mean we own it. Back to the topic: there’s definitely an issue with organization. Could I do it better? Probably not, but I didn’t run for a seat in the city government.

If it sounds like I’m ragging on the city too hard, it’s just a form of tough love: for someplace where the average annual snowfall is less than one of two storms that hit it in a week, it’s doing pretty well. Don’t just take a pat on the back, though: there’s still work to be done, and ways it can be better. And for city residents, it’s hard to refrain from being shrill, but many snow-removal crews have been working pretty damn hard to make up for a lack of preparation, so a little slack might not be out of order.

After futzing around a bit, and once again having my suspicions confirmed, I came up with the following solution to my session variables problem: temporary tables. They are dropped at the end of the session, so it all pans out nicely. I still didn’t like having to make front-ends do all the work (plus I was all excited after figuring it out), so I slapped together a pretty basic couple of wrapper functions:

/*
 * Session Variables in PostgreSQL via PL/pgSQL
 * Written/tested on version 8.4.2, but should work anywhere
 *
 * Code written by Stephen "sycobuny" Belcher
 *
 * Free to use, I enjoy writing stuff like this.
 * Just give me some props if you do.
 *
 */

/* -- These lines may not be necessary, lang/schema may exist

   -- Remove commenting only if they need to be created

CREATE
  LANGUAGE 'plpgsql';

CREATE
  SCHEMA SV;

 */

-- Make sure the session_variables temporary table exists

-- TODO: Make sure it has the right columns, too
CREATE OR REPLACE
  FUNCTION SV.ensure_session_table_exists()
  RETURNS VOID AS
$BODY$BEGIN
  PERFORM *
    FROM pg_catalog.pg_class
    WHERE relname = 'session_variables' AND
          relnamespace = pg_catalog.pg_my_temp_schema();

  IF NOT FOUND THEN
    CREATE
      TEMPORARY TABLE session_variables (
        "key" TEXT PRIMARY KEY,
        "value" TEXT
      );
    RETURN;
  END IF;
END;$BODY$ LANGUAGE 'plpgsql';

-- Set a variable. Yep.
CREATE OR REPLACE
  FUNCTION SV.set(IN xKey TEXT, INOUT xValue TEXT) AS
$BODY$BEGIN
  PERFORM SV.ensure_session_table_exists();
  PERFORM *
    FROM session_variables
    WHERE "key" = xKey;

  IF FOUND THEN
    UPDATE session_variables
      SET "value" = xValue
      WHERE "key" = xKey;
  ELSE
    INSERT
      INTO session_variables ("key", "value")
      VALUES (xKey, xValue);
  END IF;

  RETURN;
END;$BODY$ LANGUAGE 'plpgsql';

-- Get a variable's value. It's just that easy!
CREATE OR REPLACE
  FUNCTION SV.get(IN xKey TEXT, OUT xValue TEXT) AS
$BODY$BEGIN
  PERFORM SV.ensure_session_table_exists();
  PERFORM "value"
    FROM session_variables
    WHERE "key" = xKey;

  IF NOT FOUND THEN
    RAISE WARNING 'Variable % does not exist', xKey;
  END IF;

  SELECT "value"
    INTO xValue
    FROM ession_variables
    WHERE "key" = xKey;

  RETURN;
END;$BODY$ LANGUAGE 'plpgsql';

From this point, you just do:

SELECT SV.set('my session variable', 'its value');
SELECT SV.get('my session variable');

It handles creating and updating the table and values independently, without any hassles.

Well, there’s a couple hassles:

If it’s the first time you access it in a session, it barfs out warnings because PostgreSQL likes to warn you when it creates new indexes and constraints implicitly; whether this causes any libraries such as ActiveRecord to croak, I’m not sure – I will be testing that one shortly at least. Also, the get() function itself throws up a warning if you try to get a variable that hasn’t been defined yet. This is because the value of any variable can actually be NULL, but there’s nothing else to return if it hasn’t been defined yet. It’s an intrinsically implementation-specific concern whether this behavior is desired, so I split the difference: you can do it, but you’re going to be tut-tutted by the database.

So, there you have it. Of course, changing the schema into which I put these functions should be trivial; I put them there because I like the simple syntactic sugar it provides, but, as they are not tied to a permanent table it should be as simple as just changing the function names. I’ll probably wind up doing it myself, as my schemas are thematically named (individual “projects” and their “code names”).

There’s one final gotcha, which I didn’t fully account for because I ran out of ideas how to ensure it continues working right: PostgreSQL processes statements directed towards temporary tables first (if you don’t specify the fully qualified name), before checking the schemas in the search_path. I’m not sure if this is an SQL-standard way of doing things; if it isn’t, I’m sure they’ll correct it at some point, and then my code will be broken. Unfortunately, I’m not entirely sure how best to perform a query on a temporary schema (which is assigned a technically-random name by the database) without constructing the query as string, which totally removes any optimization done by preparing the statements ahead of time. If there’s some way around that, I’d be quite interested to hear it. Oh yeah, and it hides your “session_variables” table, if you’ve made one yourself. Sorry. Qualify your names and it won’t be a problem, though.

Actually, in spite of the melodramatic post title, figuring out triggers in PostgreSQL has been relatively painless. Of course, I had a pretty firm grasp of them in MySQL, and thus the major migration headache is realizing that the code handling the trigger has to be defined separate from the trigger itself.

The issue for me, however, is that (unlike Peter Eisentraut)  I have a sordid love affair with schemas. For me, it’s not just about addressing the potential naming conflicts, but breaking down the tasks our database/frontend performs into more manageable blocks. Put it this way: I have over 100 tables, functions aplenty, and datatypes (as ENUM now has to be a datatype) to spare. While I recognize that it’s not impossible to manage, and there’s many systems that probably have a volume of data far exceeding mine, it still does wonders for my sanity if I can break those down so I only have to look through at most 25 tables at a time. They’re not randomly selected, they are geared towards similar ends and logically fit together, and I think schemas really helps on that front.

My solution to Peter’s problem of localized search paths is obtuse, but it works: I deliberately name the full path in anything that’s going inside a stored procedure. It’s a very tedious and defensive posture, but it has worked pretty well, at least up until now. This is where we get back to triggers: triggers in PostgreSQL are not named according to the same conventions as almost everything else in the DBMS. While most times, in the docs “simple_name” by itself is tantamount to “public”.”simple_name” (as the default search path is ‘”$user”, public’), this is not so for triggers. They are associated with the tables for which they are defined. While in hindsight, this makes sense, it took some time to figure this out (also, Michael Graziano pointed me in the right direction after I bitched about it). What would have been far more simple is if, anywhere in the documentation, they had simply specified this strange behavior. Even a hint, when executing “CREATE TRIGGER my_schema.do_something” other than a bland “syntax error at ‘.’” would be nice.

The other part of my problem with triggers doesn’t have anything to do with the trigger mechanism itself, but an issue I’ve encountered in the database as a whole. There is, as far as I can tell, no mechanism for creating connection-level or database-level custom variables. You can make variables obviously in any of the procedural languages. However, setting a variable, like MySQL’s “SET @@my_custom_variable := ‘some custom value’;” just doesn’t seem to exist. While this may not seem like a feature that would be particularly useful (after all, there are the aforementioned procedural languages), I’ve been finding it quite problematic.

When we were on MySQL, we used wxPerl and wxRuby as front-ends to connect directly to the database using database-level logins, and I was able to write auditing fairly handily: not only could I have information about the table on what data was modified and when, I could also log who modified the data. That doesn’t seem all that amazing, except that this was all database-side. The clients had to change exactly 0 code. You could even optionally issue a “@@COMMENTS := ‘my editing comments’;” and have it apply to all of the changes automatically until you unset it, providing an easy-access way to comment the audit log. I knew this was going to be problematic switching to rails, as ActiveRecord only ever connects as one user, and uses special models to manage logins. However, with MySQL, I could just ensure that the client issued a “@@CURRENT_USER := ‘whomever@address’;” before starting work. This, while obtuse, still has the database doing the majority of the legwork. I could easily reject any statements that occured before that variable was assigned, to make sure that changes made should always include complete auditing data.

PostgreSQL has denied my attempts at a simple solution to this, however. I’ve been searching, but the easiest solution seems to be to make a special table which holds “current connection” information and somehow tie the connection ID to the user ID that way. This seems pretty complicated, and I’m not sure how safe it is to assume that the connection ID will be unique for an arbitrary length of time (as I’m not sure what mechanisms exist for periodically purging the table of stale IDs). If there is any other way around this, I’d love to hear about it.

I’ve gotten an uptick in comments on my blog in the past few days. Like, I’ve gotten three. From three different people. That’s an increase of hundreds of percent over the norm. What I found most odd, though, was that 66 percent (i.e., two) of those comments were on my ResearchSaves.org blog post. I find this very curious.

My curiosity led me to Google, whereupon I realized that my humble blog post with its stupid subtitle (which I still need to change, so, please give me ideas) is the second hit for ResearchSaves.org. What I haven’t been able to find, is whether there’s been a recent upswing in marketing from them. It seems that’s the only reason people would be taking a sudden interest.

Of course, despite repeated comments from rakaur (Eric), I haven’t changed my stance. In fact, I don’t think he really said much that I didn’t already think about the whole thing, although he had a much more defend-the-science approach to it all. I’m not going to advocate animal testing. I know it saves human lives. It ends animal lives in the meantime. Predators in the wild also end animal lives, though usually less slowly and painfully (usually). The sooner we get a viable alternative working to animal testing, the better. I’d give ideas for the alternative, advanced simulations, etc., but that’s what we pay the scientists for.

Incidentally, according to Twitter, at least, ResearchSaves.org is related to the Foundation for Biomedical Research. This is hilarious to me, as I’ve received a few E-mails from them titled “HORSE VIDEO.” I’ve never watched it, but it has something to do with using equine biological research to cure some minor ailment. It takes a good few paragraphs to get to that description though, which led me to believe for quite some time that, not only had I gotten bestiality porn, but that whoever made it was really excited about it.

Finally, in closing: no, the place I work doesn’t have any sort of established official opinion as far as I know about this whole thing. I say this because plenty of jerkoffs, jackasses, and shitheads like to politicize science and use any excuse to choke off funding to work that can actually save lives. They get understandably very antsy about it at work so, suffice it to say, I don’t speak for them.