Postgres lockup on ALTER TABLE

You can learn a lot when you break things.

I had a bit of fun and panic with Postgres the other day, while trying to do the equivalent of this innocuous seeming drop column query:

ALTER TABLE aschema.sometable DROP COLUMN IF EXISTS unwanted_column;

So innocuous was this query that the column didn't even exist in production. It had already been dropped. I was checking in an Alembic migration to keep staging and dev DBs in line with prod. Harmless, right?

The deploy went live and things started going from green to red. Uh oh.

Well, it turns out that even a non-consequential ALTER TABLE operation takes a table lock. ...which should be totally fine, unless some long-running process has taken out an AccessShareLock and is sitting on it. There's a nice write-up on this exact situation here:

The [ALTER TABLE operation] tries to take the ACCESS EXCLUSIVE it needs, and it queues up behind the first lock. Now all future lock requests queue up behind the waiting ACCESS EXCLUSIVE request. Conceptually, incoming lock requests which are compatible with the already-granted lock could jump over the waiting ACCESS EXCLUSIVE and be granted out of turn, but that is not how PostgreSQL does it.

...in other words, screeching halt - database frozen. Crap!

Postgres committer Josh Berkus wrote a pair of articles on the topic (ALTER TABLE and downtime, part 1 and ALTER TABLE and downtime, part 2). Great, now what?

Luckily, Postgres provides means to find and kill errant tasks. So, let's find and cancel the blocked ALTER TABLE query and/or the guilty part holding the shared lock.

In case you're ever in the same situation, here are a few key PostgreSQL incantations:

What locks are being held?

select d.datname, t.relname, l.relation, l.locktype, page, virtualtransaction, pid, mode, granted
from pg_locks l
left join pg_stat_all_tables t on l.relation=t.relid
left join pg_database d on l.database=d.oid
order by d.datname, relation asc;

What locks are being held on a particular table?

select * from pg_locks where granted and relation = 'my_table'::regclass \x\g\x

Who's breaking my database and what do they think they're doing?

select * from pg_stat_activity where pid=1234567 \x\g\x
select pg_cancel_backend(pid);

See what you get when you break my database?

select pg_cancel_backend(1234567);

...and finally, thanks to our friend pg_cancel_backend, our DB is unfrozen and we can go track down who had that lock held for so long.

How to grep git commits

From the There's-got-to-be-a-better-way Department, comes this tale of subterranian Git spelunking...

It all started because I lost some code. I had done my duty like a good little coder. I found a bug, wrote a test that reproduced it and filed an issue noting the name of the failing test. Then, some travel and various fire drills intervened. Memory faded. Finally, getting back to my routine, I had this bug on my docket. Already had the repro; should be a simple fix, right?

But where was my test? I asked Sublime to search for it, carefully turning off the annoying find-in-selection feature. Nothing. Maybe I stashed it. Do git stash list. Nothing that was obviously my test. Note to self: Use git stash save <message> instead of just git stash. I did git show to a several old stashes, to no avail. How am I going to find this thing?

A sensible person would have stopped here and rewritten the test. But, how much work are we willing to do to avoid doing any work? Lots, apparently.

So, I wrote this ridiculous bit of hackery instead. I call it grep_git_objects:

import os
import sys
import subprocess

if len(sys.argv) < 2:
    print "Usage: python grep_git_objects.py <target>"
    sys.exit()

target = sys.argv[1]

hashes = []
for i in range(0,256):
    dirname = ".git/objects/%02x" % i
    #print dirname
    for filename in os.listdir(dirname):
        h = "%02x%s" % (i, filename)
        hashes.append(h)

        cmd = "git cat-file -p %s" % h
        output = subprocess.check_output(cmd.split(' '))
        if target in output:
            print "found in object: ", h
            print output

Gist: grep_git_objects.py

Run it in the root of a repo. It searches through the .git/objects hierarchy to find your misplaced tests or anything else you've lost.

You'd think git grep would do exactly that. Maybe it does and I'm just clueless. I hope so, 'cause otherwise, why would you have a git grep command? In any case, git grep didn't find my missing test. The above bit of Python code did.

Once I found it, I had the hash code for a Git object. Objects are the leaves of Git trees. So, I grepped again for the hash code of the object and found the tree node it belonged to, working my way up a couple more levels to the commit.

With commit in hand, I could see by the "WIP on develop" message that is was indeed a stash, which I must have dropped by mistake. Anyway, that was fun... sorta.

Clojure Koans

In an attempt to reach a bit higher plane of enlightenment with respect to Clojure, I did the Clojure Koans. What a great way to get familiar with a new language.

It might be worth watching the video solutions: Clojure Koans Walkthrough in Light Table.

Special secret stuff with s3cmd

According to my coworker, "Amazon's S3 is the best thing since sliced bread." For working with S3, s3cmd is a handy little tool. It's documentation is a bit on the sparse side, but, what do you expect for free?

One gotcha with S3 is that buckets and the files in them have entirely distinct ACLs. This can lead to scenarios where the owner of a bucket can't work with the files in it. An easy way for this to come about is to log into the S3 console and create a bucket with one set of credentials, then upload files with a tool like s3cmd under another set of credentials.

cd /path/to/files/
s3cmd -v sync . s3://damnbucket.bucketowners.org

You can give permissions to the bucket owner like so:

s3cmd setacl --acl-grant=full_control:i_own_the_damn_bucket@bucketowners.org --recursive s3://damnbucket.bucketowners.org

You might also want to make the files public, so they can be served as a static website.

s3cmd setacl --acl-public --recursive s3://damnbucket.bucketowners.org

AWS Command Line Interface

I've been using s3cmd for a while, out of habit, but maybe it's time to try to Amazon's AWS Command Line Interface, which just had their version 1.0 release.

From a brief look, AWS CLI looks nice. You can do the same sync operation as above and make files public in one command:

aws s3 sync . s3://damnbucket.bucketowners.org --acl public-read

Amazon is very mysterious about how to specify the target of a grant of permissions, aka the grantee. I tried to give permission to the owner of a bucket, but kept getting an error. Some more examples in the docs would help! I also get "Invalid Id" for no apparent reason in the permissions section of the web UI for S3, so maybe I'm just clueless.

aws s3api put-object-acl --bucket damnbucket.bucketowners.org --grant-full-control i_own_the_damn_bucket@bucketowners.org --key genindex.html
#> A client error (InvalidArgument) occurred: Argument format not recognized.

As far as I could tell, the AWS CLI tool seems to be missing the --recursive option that we used with s3cmd. That seems like a fairly essential thing.

Also, I couldn't get the profile feature to work:

aws s3 ls s3://damnbucket.bucketowners.org --profile docgenerator
#>The config profile (docgenerator) could not be found

NOTE: Many thanks to Mitch Garnaat, I now know how the --profile switch works. Contrary to the documentation, you need a heading in your config file like this: [profile docgenerator] rather than like this: [docgenerator].

I'm glad Amazon is taking the lead in developing this tool, and I'm sure they'll keep making it better. And, there's a Github repo, so I'm guessing that means they take pull requests.

RESTful APIs

At Clojure-West, this past March, former ThoughtWorker Siva Jagadeesan spoke on on how to build good web APIs using Resource Oriented Architecture (ROA) and Clojure.

The talk isn't really specific to Clojure, but is really more a primer on REST APIs.

Rants about true REST versus REST-ish, REST influenced, or partially RESTful are not especially interesting. That's not what this is. It's a nicely pragmatic guide to the architectural patterns and why you might use them.

1. The swamp of POX (plain old XML, or more likely JSON, these days): At first glance, many, myself included, take REST to mean RPC over HTTP with XML/JSON.
2. URI: Start thinking in terms of resources.
3. HTTP: The HTTP verbs together with URIs define a uniform interface. CRUD operations are always handled the same way. So are error conditions.
4. Hypermedia (HATEOAS): Possible state transitions are given in the message body, allowing the logic of the application to reside (mostly? completely?) on the server side. The key here is "discoverability of actions on a resource."

RESTful Clojure by Siva Jagadeesan

More

• Richardson Maturity Model: steps toward the glory of REST
• Roy Feilding's REST paper
• Clojure tools for REST APIs: Bishop and liberator
• Christopher Miles on Bishop: Makes Your Web Service Shiny and Why Is My Web Service API Crappy?
• REST and designing APIs
• Clojure: Web Socket Introduction
• RESTful principles
• Exploring the implementation aspects of the REST architectural style
• BlueEyes
• What Makes a Great Open API?, a talk from OSCON 2012 by John Musser of ProgrammableWeb
• Atlassian REST API Design Guidelines
• The REST API Design Handbook
• A 2006 article, Tim Bray on REST
• Versioning: how to express this in a RESTful way? domain.org/myresource/version/1 implies that version 2 is a different resource. Would it be better to express versioning as an orthogonal dimension? A URL defines a resource, we set a header to ask for a version?

Javascript style objects in Python

As I've gotten older and crankier, one of the things I've gotten cranky about is Object Oriented Programming. Sometimes, it seems like rigid class hierarchies just get in the way. Sure, it's cool that the compiler can prove type-safety properties about your program, but whether the boilerplate is worth the benefit is situation dependent. When it just has to work once, coding for maintenance is a poor investment - quick-n-dirty is the way to go.

I've found class hierarchies in dynamic scripting languages to be especially unnecessary. Why use a language where you're not supposed to worry about typing to build elaborate user-defined type hierarchies?

In those quick-n-dirty scenarios, what I want is a big stinking bag of properties. Ideally, I don't care whether the values are data or functions. In other words, just like it's done in Javascript.

Here's one way to get something like that, in Python:

The future of dev tools

Classic developer tools have a timeless quality to them. Greybeards and college kids alike happily hack away in Emacs, vi, Bash and a host of other tools older than many of their users. It's surprisingly hard to improve upon these old tools.

But, interest in designing new developer tools seems finally to be emerging. Rather than replacing powerful and expressive textual interfaces with pretty but limiting graphical interfaces, these new tools augment the command-line experience with immediate visual feedback.

Light Table

Chris Granger's re-imagining of the IDE, Light Table, seeks to enhance the developer's "ability to traverse abstraction". Slick demo videos here and here show Light Table's good looks, but also its presentation of functions as the primary unit of abstraction, the ability to show values propagating through code and its handy access to documentation.

During Granger's talk at StrangeLoop, one questioner raised the issue of whether dynamic languages lead to a different style of interaction between tool and developer. Static languages lend themselves to autocompletion and refactoring tools as in Eclipse, whereas dynamic languages emphasize the REPL and perhaps metaprogramming and DSLs.

Though originally funded through KickStarter project, Light Table is closed-source, at least for now. It's scheduled for release next May with support for Clojure, Javascript, and Python.

Neo4j

Modern browsers provide capabilities like accelerated graphics, advanced page layout and process isolation, enabling environments like Neo4j's console demo that combine command shells with interactive graphics.

As far as I can tell, that's just a demo. The real admin console for Neo4j doesn't suck either, but requires tabbing between command shell and graph visualization.

Dev tools in browsers

Browsers keep getting better. In-browser REPLs exists for numerous languages: clojure, haskell, javascript and others. These are typically targeted at language learners, as is Chas Emerick's Clojure Atlas, a visual and conceptual interface for navigating Clojure's documentation. But, I expect more advanced tools will find their way into the browser over time. Fogus's Himera project shows one way forward, delegating some of the heavy lifting to the server.

Amazingly, R Studio Server puts full IDE into the browser, with the help of QTWebkit, GWT and some grand-master level wizardry. With Knitr integration, R Studio approaches live document capabilities.

Sublime Text

On the desktop, the Sublime Text editor picks up where TextMate left off. Sublime can use syntax files from TextMate, which means it already supports your favorite language, plus it's programmable in Python.

Xiki

I heard about Xiki (for executable wiki) from Tom Henderson, who owes me a book, by the way. An impressive demo video shows off Xiki's merger of advanced UI and command shell.

Xiki integration with Sublime is progressing.

Design principles for programming tools

Bret Victor, known for his design work at Apple and lots of other cool things, has thought deeply about using technology to help people “learn, understand and create”. Victor followed up the inspiring talk Inventing on principle his presentation at StrangeLoop on design principles for programming tools, captured in the essay Learnable Programming.

Tools should enable the programmer to:

• Read the vocabulary
• Follow the flow
• See the state
• Create by reacting
• Create by abstracting

What this means is roughly this: Quick access to docs, often triggered by mouse-over, simplifies reading. Visibility into flow and state increases comprehension. “Dumping the parts bucket onto the floor&rduo; encourages mixing and matching and provides visual prompting emphasizing recognition over recall. Abstractions are created by starting concrete and generalizing.

Code is written for a dual audience: machine and human reader, requiring a difficult combination of precision and clarity. As the tools get smarter, the conversation between machine and programmer will get richer. The common thread here is supporting the programmer without imposing limitations, providing an experience more like a blank page and a box of sharp pencils than a menu of canned options, helping to create what Bret Victor calls, “environments that function as an external imagination”.