Django

I’ll be presenting a full-day tutorial on Django Development with PostgreSQL at PostgreSQL Conference East, March 22-25 in New York!

tl;dr: If you are doing a .distinct() query and limiting the results using .values() or .values_list(), you may be in for a surprise if your model has a default ordering using the Meta value ordering. You probably want to clear the ordering using .order_by() with no parameters.

If a model is ordered, either

tl;dr: Don’t retrieve a whole row just to get the primary key you had anyway. Don’t iterate in the app; let the database server do the iteration for you.

There’s a couple of bad habits I see a lot in Django code (including, sadly, my own), which is abuse of a ForeignKey field. Let’s take

tl;dr: You can’t compare NULLs. A nullable primary key is a contradiction in terms. You can’t join on NULL, so a NULL foreign key refers to nothing, by definition. NULL doesn’t do what you think it does, no matter what you think it does.

NULL in SQL is annoyingly complex.

There’s really no conceptual

This is a follow-up to the previous post, in which we talked about ways of handling huge result sets in Django.

Two commenters (thanks!) pointed out that psycopg2 has built-in support for server-side cursors, using the name option on the .cursor() function.

To use this in Django requires a couple of small gyrations.

First, Django

tl;dr: Don’t use Django to manage queries that have very large result sets. If you must, be sure you understand how to keep memory usage manageable.

One of the great things about modern interpreted, garbage-collected languages is that most of the memory management happens behind the scenes for you. Unfortunately, sometimes, the stage equipment comes crashing

A well-known issue that can come up with Django sites running on PostgreSQL is that connections in “Idle in Transaction” state can pile up. There’s a relatively straight-forward fix, but ultimately, it’s due to a bug in Django’s transaction management, at least when PostgreSQL is the back-end.

Let’s run through it.

First, what’s the “Idle in Transaction”

Here are the slides for my talk at PGXPUG Day OSCON 2010.

In part 1, we ran down a list of the standard Django features for controlling transactions. Now, we’re going to look at some ways to optimize how these tranactions happen.

Let’s look at the SQL that our create_order() view function generated, with the transaction middleware turned on, and no transaction decorators on the function:

1
 

Django has quite a bit of code in it devoted to transaction management. Although the documentation goes into quite a bit of depth on transactions, I’ve never felt that the docs by themselves let you build a good mental model of how transactions actually work. So, I decided to approach it experimentally: Build a small Django app, and see