This post originated from an RSS feed registered with Ruby Buzz by . Original Post: On Long Transactions Feed Title: cfis Feed URL: http://cfis.savagexi.com/articles.rss Feed Description: Charlie's Blog Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Latest Posts From cfis

It was with great interest that I read that PostGIS 1.1.3 supports long transactions. Except there is one problem - if you dig into the documentation you'll see it does no such thing.

Instead, PostGIS supports record level locking as defined in OGC's Web Feature Service (WFS) specification. According to the spec (section 10, page 34):

The purpose of the LockFeature operation is to expose a long term feature locking mechanism to ensure consistency. The lock is considered long term because network latency would make feature locks last relatively longer than native commercial database locks.

This has nothing to do with long transactions, and really should be called something like "record locking"

So What is a Long Transaction?

The term long transaction came out of the GIS industry to describe updates that take days or weeks or months to complete. It was coined to highlight the difference between normal database transactions, or "short transactions," that take milliseconds to complete.

Most of what we do on computers are long transactions - writing documents, creating spreadsheets, drawing graphics, writing new software, etc.

In the GIS world, long transactions are crucial for modeling the world. For example, imagine a developer wants to build a new subdivision. Part of the required work is to design the subdivsion's networks - roads, water pipes, sewer pipes, electrical lines and phone lines. Another part is to lay out the parcels - where the houses will go. Creating these designs can take months, and it is often necessary to create several different designs to find the optimal one.

While this works is being done, you want it to be isolated from other users so as to not disturb their work.

Versioned Databases

Two naive ways of implementing long transactions are:

• Locking
• Check in and check out

Both of these approaches were tried in the industry, and unsuprisingly, failed. The problem is that they don't scale in multi-user systems. Before long, users start stepping on each other toes and the whole system grinds to a halt.

Instead, what is needed is an approach that allows users to create their own "version" of the database, work on it as long as needed, and once its done, merge it back into the main database. If you are a developer, this should sound awfully familiar. Its the exact same functionality that branches in source control systems provide.

Implementations

Smallworld was the first commercial implementation of a GIS that had a versioned database that supported long transactions. Later, Oracle, working with Smallworld, introduced a similar technology in Oracle 9i called which they called Workspace Manager. ESRI, the largest GIS vendor, also now supports long transactions.

Unfortunately, Postgresql/PostGIS does not support versioned databases. And the new locking functionality it provides is almost useless because it won't scale in multi-user environments. Of course, the PostGIS developers are just implementing a poorly thought out part of the WFS specification.