Thom's memory leak

There have previously been various posts on Writable Common Table Expressions (wCTEs) which demonstrate either performance gains or simple examples of how they work, but recently when giving some training, I wanted to come up with some new material to show folk that demonstrates an example of when you might wish to use it. Note that writable common table expressions are only possible in PostgreSQL, and only from version 9.1 onwards. But because 9.1 has been out for almost 2 months now, that means you can go and use this feature straight away! So in case you don't know what a writable common table expression is, in summary it's the same as a normal common table expression, except that you can also use UPDATEs, DELETEs and INSERTs with it. If you don't know what a normal common table expression is, it effectively let's you make the results of queries available to other queries in the same statement. What follows is a rather basic case, but the data is kept simple so as...

The first round of PostgreSQL Conference Europe talks have now been selected and already it's shaping up to be a great conference if these are any to go by. You can see the list so far at http://www.postgresql.eu/events/sessions/pgconfeu2011/ . If we take a brief look at a few of the talks, you'll see that you really don't want to miss this year's line-up. Firstly we have a talk by PostgreSQL veteran and core team member Bruce Momjian who will be "Explaining the Postgres Query Optimizer". This will enlighten attendees as to how PostgreSQL makes its decisions in response to queries which is important to understand in order to know how best to ask PostgreSQL what you want. Bruce gave a very popular MVCC talk last year, and no doubt this one will be one to watch. Also, Michael Meskes's talk entitled "Mission impossible? Can I replace my most important databases with PostgreSQL?" won't be a brief "yes", but will elaborate on Mi...

Peter Geoghegan (@sternocera) has just joined us on Planet PostgreSQL but unfortunately his post (which looks like he's put quite a bit of work into it) became too old to list on the main site's front page before his account was approved, so here's me waving my arms to give you a second chance to find it. You can read it here .

So I went to the No2AV website, and clicked on the link entitled "Why vote no" to see what reasons they've cooked up. Let's take a look: AV is costly They argue, as they keep arguing, that AV will cost up to an additional £250 million: "Local councils would have to waste money on costly electronic vote counting machines and expensive voter education campaigns." What about the extra £800 million they'll also have to spend on red carpets, gold-trimmed ballot papers and free meringue and strawberries served to voters as the booths? What about those? Yes, of course, there's no such cost, because they won't be used. No-one said they would need to be used. They harp on about how Australia is the only other country to use AV to vote in the lower house, but they conveniently ignore the fact that Australia doesn't even use electronic voting machines. "With ordinary families facing tough times can we really afford to spend a quarter of a bill...

I returned from Brussels with Dave Page yesterday where I attended FOSDEM. It was my first time at this event and had a great time. Loads of organisations were represented there including Mozilla (promoting Firefox), LibreOffice , OpenOffice, Debian, OpenSUSE , Ubuntu , Fedora , FreeBSD, Perl, FSFE, Mandriva, Gnome, KDE, CentOS, CAcert, and many others. And of course, PostgreSQL, where I was helping out, selling various items and talking to people about Postgres itself. All the plush blue elephants were sold (and there were quite a lot), all the stress balls sold, many t-shirts and backpacks sold, and all the pins and pens were given away too. In fact the last pin was taken by a friendly Monty Widenius (creator MySQL and now MariaDB) which he put on there and then. Perl, who were our next-door neighbours, bought one of the plush PostgreSQL elephants and placed it atop their massive Perl camel , which we then declared made it pl/Perl . And one person even bought a batch of 100 str...

FOSDEM is running this weekend in Brussels, and there are a plethora of talks to attend. Naturally I'll be very interested in the PostgreSQL-related talks, and they are as follows: Sunday - 10am PL/Parrot - David Fetter will be giving his talk about this new procedural language addition. I don't actually know much about this, which is why I'm especially interested. Asynchronous Notifications for Fun and Profit - Marc Balmer's talk on asynchronous notifications in PostgreSQL will cover message brokering in distributed environments. This is, annoyingly, at the same time as David's talk. Sunday - 11am PostgreSQL extension's (sic) development - Dimitri Fontaine will be sharing his expertise on extension development, hopefully covering the subject of what he's been hard at work on: extension management within PostgreSQL itself. Sunday - 12pm Writing a user-defined type - The scarily clever Heikki Linnakangas will explain how you can create your own data t...

The upcoming PostgreSQL 9.1 release is packed full of awesome new features, but let's not forget the performance improvements which will also arrive with it. Every version of PostgreSQL brings about performance improvements in various areas, and 9.1 is no different. Here's a brief overview of some, but not all, of the changes affecting performance: Checkpoints now with less spikes When checkpoints occur on a database, they can result in an I/O spike. 9.1 has the option to spread the checkpoint fsync calls out so that the system doesn't grind to a halt when flushing. In addition to this, there's been a change which will remove duplicate requests to the background writer, meaning even less I/O, so checkpoints are now lighter and smoother than ever. These changes haven't yet made it in, but they're in-progress. Integer to text casts quicker The conversion of a signed integer to text now happens in hand-coded internal code as it's faster than the previou...