Show blocking Postgres processes and kill them

Have you ever had to kill your Postgres cluster because one hanging client starved other processes until most clients became unresponsive blocking on this one pesky process?

There is a very nice way to show currently blocked queries and the processes those are blocking on slightly adapted from this query posted on the Postgres mailing list. I suggest putting it into a view so you can easily access it when you need it:

CREATE VIEW blocking_procs AS
SELECT 
    kl.pid as blocking_pid,
    ka.usename as blocking_user,
    ka.current_query as blocking_query,
    bl.pid as blocked_pid,
    a.usename as blocked_user, 
    a.current_query as blocked_query, 
    to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a 
        ON bl.pid = a.procpid
    JOIN pg_catalog.pg_locks kl 
        ON bl.locktype = kl.locktype
        and bl.database is not distinct from kl.database
        and bl.relation is not distinct from kl.relation
        and bl.page is not distinct from kl.page
        and bl.tuple is not distinct from kl.tuple
        and bl.virtualxid is not distinct from kl.virtualxid
        and bl.transactionid is not distinct from kl.transactionid
        and bl.classid is not distinct from kl.classid
        and bl.objid is not distinct from kl.objid
        and bl.objsubid is not distinct from kl.objsubid
        and bl.pid <> kl.pid 
    JOIN pg_catalog.pg_stat_activity ka 
        ON kl.pid = ka.procpid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

How to test the query on a testing server (not your production DB server)

Connect to your database open a transaction and manually lock a table:

BEGIN;
LOCK your_table;

Leave the transaction and connection open.

Open another client that accesses that data:

# SELECT count(*) from your_table;

It now should be blocked.

View the currently held locks with a third client:

# SELECT * FROM blocking_procs;
blocking_pid   | 25842
blocking_user  | postgres
blocking_query | in transaction
blocked_pid    | 25844
blocked_user   | postgres
blocked_query  | SELECT COUNT(*) FROM "your_table"
age            | 00h:00m:23s

It's now possible to kill the offending process holding the lock using:


# SELECT pg_terminate_backend(25842);

This will kill the connection where you've set the lock and the open transaction is rolled back but it seems to leave everything else intact. The second client should now get the response from the server.


Tested on Postgres 9.1.

Android apps and databases

As a web developer I was always intrigued to learn more about mobile app development. Unfortunately, my early attempts with my first smartphone - a Palm Pre running Web OS - suffered from company politics and I decided to stop developing apps for an awesome but abandoned platform. So here I am - roughly two years later - contemplating to buy a new smartphone.

While my Pre Plus is still working the lack of apps and slow demise of the underpowered hardware became too annoying. For a change, I didn't want to go with an underdog and ended up ordering a Motorola Razr i. While waiting for the shipment to arrive I started writing my first app for Android 4. The first lesson I learnt is that looking after a wailing baby leaves little time and less focus to get anything done on top of that.

Then there's Java - an old acquaintance from more than a decade ago. I never liked it, never hated it either, but I've always been put off by the enterprise nature of everything surrounding it. It's also one of the few languages that requires an IDE. After a few hours trying to put together the simplest Android app with vim and a console and failing to figure out simple things like where to import classes from I installed Eclipse.

I must admit Eclipse does not suck as much as it used to (as in the room's light bulbs do not dim anymore when you fire it up). With Eclipse things got more fluent and I especially liked the way the UI designer works and keeps you at a safe distance from XML. I must admit I haven't used a visual UI designer that actually worked since Delphi 4. Everything was going well until it came to persisting structured data. You would assume that in 2013 a smartphone with its abundance of sensors has an OS that makes aggregating and sharing that data as easy as possible. So how do you store structured data? Easy: Just use sqlite and implement DAOs for your tables. It's as easy as that. Seriously, I feel like someone has given me the latest Makita multitool and just a bunch of nails and expects me to build some awesome stuff with that.

Maybe I spent too much time with web frameworks like TurboGears and Django but reinventing (shitty) ORMs for every app I write is not something I want to do in my free time. Scratch that: I don't want to do that at all. And this is just for storing data locally. I probably want to sync that data to the cloud, a web application or other devices later on.

Maybe I should just save data to a remote web application? The question then is: Why bother with a mobile app at all? Sounds like pouring your time into making one web app work well with mobile devices is time better spent than implementing about four different apps for the currently relevant mobile platforms. Plus this gives you the option to use your favorite language and best available technology to implement the web app.

That sounds like a good idea until you want to make the app work in offline mode or with low latency in the outback of Brandenburg (make that Wyoming if you're in the US). Then building a dedicated app is the only option but can be limited to the features that are relevant in these usage scenarios, e.g. gathering data like miles walked through the woods or number of diapers changed can be logged in the mobile app but displaying statistics can be restricted to the web application. That's what apps like Baby Connect seem to do (yes, that's the most interesting app for me right now). I'm just amazed on what kind of libraries these things are built on.

IMHO it doesn't have to be that complicated. Android could sync CSV data to a Google Drive spreadsheet which would make sharing data with different devices easy and wouldn't require a dedicated web application.

One could also let apps connect to a pre-installed SQL database running on the device and make a light-weight ORM part of the standard library. This wouldn't solve the problem of syncing the data to a remote server but wouldn't require ridiculous amounts of code to solve simple problems.

Ultimately I could also imagine having a distributed database node (let's say Riak) on the device and which would allow syncing with remote nodes.

Do I ask for too much? Maybe that's already possible in Android and I just didn't get it? Or maybe the grass is greener on the other side and these things have been solved in iOS, Windows Mobile or BlackBerry?

Postgres deadlock debugging

Update:  There is a better way to view Postgres locks and blocking chains.

Found a nice SQL query for debugging deadlocks hidden deep in the postgres docs:

select
pg_stat_activity.datname,
pg_class.relname,
pg_locks.transactionid,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
substr(pg_stat_activity.current_query,1,40) as current_query,
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;

Cyberpunk coding fonts - monofur on Linux

If you ever grow weary of Inconsolata, try monofur. Adding Truetype fonts to Linux is a piece of cake with fontypython. Just install it via Synaptic or apt-get.

Danmark

La Joconde

How to compile Vim on Linux

http://users.skynet.be/antoine.mechelynck/vim/compunix.htm

Klartext

SQL for removing invalid foreign keys and typecasting in Postgres

DELETE FROM session_data WHERE session_data.name = 'user_id'
AND NOT EXISTS
(SELECT * FROM users WHERE CAST(session_data.value AS integer) = users.id);

Debugging nosetests with ipdb

Ever wanted to debug Python with tab completion and syntax highlighting? Then you'll love ipdb:

sudo easy_install ipdb

The only thing you have to do is use ipdb instead of pdb:

import ipdb; ipdb.set_trace()

With a little trick it will even work with nosetests:

import sys; sys.stdout = sys.__stdout__; import ipdb; ipdb.set_trace()

Wanda's Wisdom

You may be gone tomorrow, but that doesn't mean that you weren't here today.

Break the Cycle: Local Class Definitions

In the process of analyzing the memory behavior of your Python application, you will sooner or later stumble across reference cycles. It is always a good idea to avoid creating reference cycles, though not every cycle is worth breaking (using weak methods avoided some reference cycles but increased the invocation cost).

Debugging reference cycles can be simplified by creating a graphical representation of the reference graph, e.g. using graphviz. Marius Gedminas provides a set of tools to facilitate building graphs at his homepage. Similar facilities exist in Pympler. The latter improved considerably since the official 0.1 release so be sure to grab the version from the svn trunk.

When you know what objects are involved in cyclic dependencies you will want to know why these occurred in the first place, which is not always trivial to figure out. While working on the integration of Bottle (which is really great BTW) in Pympler, I stumbled across an interesting case:

import gc
gc.disable()

def f():
    class Foo(object):
        pass
f()

from pympler.gui.garbage import GarbageGraph
GarbageGraph(reduce=True).render('cycle1.png', format='png')

This snippet creates the following reference cycle (click on the image to enlarge):



Apparently, defining a class in the local scope of a function or method creates a reference cycle. Lifting the class definition to the module level avoids the reference cycle. It is even more interesting that class objects create reference cycles by design when they go out of scope:

>>> import gc
>>> gc.disable()
>>> class Foo(object):
...   pass
>>> del Foo
>>> gc.collect()
6

So what? Well, it is evidently beneficial to define classes in modules or other classes, and not in functions or methods.

Substitute assert statements with unittest methods using vim

In the Python community, it's not generally agreed upon whether to use the assert statement or the assert* methods from the unittest module. As some commentators pointed out in a recent discussion, there are (a few) good reasons to prefer the assertion methods, e.g. better error messages.

Here are some vim substitution commands that make the transition from assert statements to the appropriate methods easier:

:%s/assert \(.\+\) == \(.\+\)/self.assertEqual(\1, \2)/gc
:%s/assert \(.\+\) != \(.\+\)/self.assertNotEqual(\1, \2)/gc
:%s/assert \(.\+\)/self.assert_(\1)/gc

Convert Images to A4 PDF

Converting raster images to PDF in a printable format can be achieved using the ImageMagick convert utility with the page parameter:

convert -page a4 *.png images.pdf

The converter, however, not quite does what I expected. Images are resized to fill the A4 page but the aspect ratio is preserved and no margin is added. This actually leads to different sized pages for images with different ratios (which is common for scanned documents for example).

In order to create equal-sized PDF pages from a bunch of images, a margin or border needs to be added to the images. Doing this manually is a cumbersome process. Therefore, I've written a little Python script which adds a (white) border to the individual images to enforce an aspect ratio compatible with A4 pages. The script creates a PDF file from a bunch of image files with uniform A4 page size:

import sys
from subprocess import Popen, PIPE

PAGE_WIDTH = 210.0
PAGE_HEIGHT = 297.0

files = [arg for arg in sys.argv[1:-1]]
output = sys.argv[-1]
tmp = ["a4%s" % f for f in files]
for f,t in zip(files, tmp):
    p = Popen(["identify", f], stdout=PIPE)
    dim = p.communicate()[0].split()[2]
    w,h = [float(d) for d in dim.split('x')]
    bw,bh = 0,0
    if w/h < PAGE_WIDTH/PAGE_HEIGHT:
        nw = PAGE_WIDTH * h / PAGE_HEIGHT
        bw = int((nw - w) / 2)
    else:
        nh = PAGE_HEIGHT * w / PAGE_WIDTH
        bh = int((nh - h) / 2)
    Popen(["convert", "-border", "%dx%d" % (bw,bh), 
        "-bordercolor", "white", f, t]).communicate() 
Popen(["convert", "-page", "a4"] + tmp + [output]).communicate()

Save the script to img2a4pdf.py and invoke it like that:

python img2a4pdf.py *.png output.pdf

Maybe someone will find it useful.

Der neueste Kick

Mal wieder eine Meldung auf Tagesschau.de:

"Es ist offensichtlich, dass unsere Milchbauern gerade leiden", sagte sie. "Es geht um echte Menschen und nicht um Statistiken auf einem Blatt Papier."

Etwas weiter unten dann:

Der deutsche Bauernverband hatte das Schlachten von Kühen als eine Möglichkeit gesehen, um das Überangebot an Milch auf dem europäischen Markt zu bereinigen: Wenn anderthalb Millionen Tiere getötet würden, könnte das den Kick geben, um aus dem tiefen Tal wieder herauszukommen.

Mit etwas Glück kommt vielleicht bald die Milch im Kaffee nach dem Steak vom selben Tier. Prost Mahlzeit!

Immerhin, das Fleisch von anderthalb Millionen geschlachteten Rindern reicht in Deutschland keine sechs Monate. Wer wäre da nicht gern Vegetarier.

Inkscape PDF Export

Inkscape is nice vector drawing program, especially for illustrating Latex documents. In the past, I've always exported to eps first and then converted the docs to PDF using epstopdf. This way, the bounding box is confined to the actual region of interest.

Unfortunately, transparency information is lost in the process. What works, though, is to fit the page to the selection just before directly exporting to PDF. Go to File > Document Properties and press Fit page to selection on the Page tab.

Almost done

At last, I've completed my final thesis. Seven exciting years as a student passed almost too quickly. In a week from now, it'll all be over. Finally, time for traveling, working on Pympler and SCons, getting an interesting job.

In the process of finding a new home, our old server will probably be disconnected before very long. Therefore, it is time to find a new haven for ideas, thoughts and casual code snippets.

Empirical Comparison of SCons and GNU Make

A course work of mine, entitled Empirical Comparison of SCons and GNU Make, is now hosted at the company site of my tutors. Thanks guys!

Sverige

Upgrade to Hardy Heron vs Trac and sqlite

Today I tried something which I don't really expected to work. After a direct upgrade from Dapper to Hardy, Trac stopped working:

"file is encrypted or is not a database"

There's a version conflict between the Trac database and the accessing sqlite version. The best solution I found while searching the web, was to convert the databases manually:

sudo -s
apt-get install sqlite
cd /var/your-trac-project/db
cp trac.db trac.db.orig
sqlite trac.db .dump > trac.sql
rm trac.db
sqlite3 trac.db ".read trac.sql"
chown www-data:www-data *
trac-admin .. upgrade

After that, Trac worked again.