I am currently working on building a new site which is migrating from an existing one.  Front-end work mostly complete, now to creating the admin.

The Problem

Today, a really quite annoying “feature” of PostgreSQL raises its head and takes up far too much time to resolve.  For some background, the data is from an old site built in PHP & MySQL, being migrated to Django & PostgreSQL. This has been working fine, the frontend works and that’s great.

To maintain the old database id’s we inserted everything en-mass into the new database. This in itself was not the problem.  The problem arose when starting to adding new row and are expected auto-increment to the end.

Just a simple query, nothing overly complicated here.

INSERT INTO table (title) VALUES ('my_title');

However, instead of actually inserting into a new row at the end, it started working from id 1. This throws up errors whenever the id already existed (this differs from MySQL). Worse still, on some occasions started to delete items from the end of the database.

The Solution

So, what’s going on? I was initially expecting it to be a problem with my code although but, after plenty of testing, that wasn’t the case.

The primary key wasn’t updating the auto-increment value as expected. Here was the simple fix (change field names where necessary). This will reset the internal counter to the next available number.

SELECT setval('"table_id_seq"', coalesce(max("id"), 1), max("id") IS NOT null) FROM "table";

If you are using Django  you can generate the SQL by running, it does not run the automatically but you can pipe the response directly into the psql tool.

python manage.py sqlsequencereset appname

Very simple, but this does not follow the MySQL (less control over increments and automatically finding the next available number). Then again SQLite also does things differently and no doubt others as well.

It really pays to be aware of some of the fundamental, often unexpected, differences between databases and frameworks. Embrace change, don’t fear it. Do your research, is it better?, does it make for a logical change? But, don’t embrace unnecessarily.

Originally, the tutorials mentioned here, were going to include the 960 grid system.  After some thought weighing up the benefits on convenience, performance, future updates etc, I have decided this will not be used for the main site.

I will still do tutorials on designing & prototyping using the grid system but that is as far as I will go.

So what’s wrong with the grid system?

As a developer who is trying to build a site that performs well, write tutorials from real experience and allow myself to easily apply updates in the future things have had to change.

• The grid system has a lot of CSS I will not personally be using in this site.  Granted I COULD remove this, but I could also create what I need myself.
• Future updates…well I intend to actually improve the site as time progresses.  The design will evolve as more content in introduced to the site, 960 may not even be wide enough, however, the problem comes when needing to adjust column widths.  Using CSS on its own, you adjust the width of the 2 columns and your down.  Using the grid system, you need to go through the HTML adjust class names.
• Without using the grid system I am also able to create another tutorial for site layouts.  The grid system is fairly self-explanatory and will be covered on the prototyping stage.
• Fluid or expanded columns.  By default the 960 grid system does not support fluid layouts, there is a branch for fluid and elastic layouts, but at this stage I am not entirely sure how or what I want to achieve with this.