Monthly Archive for July, 2006

Comparing package versions in PostgreSQL

Published
by
Gustavo Niemeyer
on 2006-07-30
in C/C++, PostgreSQL, Project and Python
. 1 Comment

This weekend I’ve played a bit with PostgreSQL extensions written in C.

A while ago I wrote a Python C extension for Smart to compare Debian package versions. Now I was trying to do something similar inside PostgreSQL, and thus ported the original Python C extension code to a PostgreSQL C extension. It enables queries like the following:

# SELECT 'Matched' WHERE deb_version_match('1.2', '<=', '2.0');
  ?column?
----------
 Matched

The implementation of the PostgreSQL C extension was quite straightforward, but I’m a bit disappointed by the performance of PL/PGSQL.

I’ve made tests using two environments. One of them is a PL/Python function executed as a trigger, which calls the original Python C extension and executes SQL back in PostgreSQL using the plpy module. The other is a PL/PGSQL function which uses the implemented PostgreSQL C extension directly.

Considering that the function logic consisted of one loop over a SELECT statement, a few tests, and an INSERT statement, I was expecting that the overhead introduced by going back and forth between the PostgreSQL state and the Python interpreter state would be a lot more noticeable when compared with PL/PGSQL executing an internal PostgreSQL function. Tests have shown about 10% of improvement roughly, when doing a similar logic over about 5000 items.

I’m not yet sure if the speed improvement pays off the limited debugging feedback provided by the PL/PGSQL interpreter on errors.

Moved to a new place!

Published
by
Gustavo Niemeyer
on 2006-07-24
in Other
. 0 Comments

After a somewhat long effort, all posts were moved to the shiny new blog on labix.org, including comments!

The new blog is based on WordPress, and brings a few new features that I was missing in LiveJournal. I of course missed control over the environment, but most importantly, I was missing tag-specific RSS feeds, so that people can keep track of topic they have interest in, rather than every topic that interests me. For instance, to keep track of Python-specific posts, one may link to:

http://blog.labix.org/tag/python/feed

The new toy makes me feel motivated to post news about interesting things I’ve been working with lately (some cool stuff is coming, but some of it will unfortunately take a bit longer to become public), and perhaps even some past work I forgot to keep track of.

I’ve also installed a WordPress plugin to crosspost entries to LiveJournal, so that new entries are still seen there. Please, update your links or remind someone to do so if possible, as this will eventually be disabled.