Magnus Hagander's PostgreSQL blog (Entries tagged as postgresql)

New committers to PostgreSQL

nospam@example.com (Magnus Hagander) — Sun, 26 May 2013 16:13:14 +0000

During the closing session of PGCon this year, the core team announced the addition of four new committers to PostgreSQL:

Jeff Davis
Stephen Frost
Fujii Masao
Noah Misch

These have all been involved in both writing new code for PostgreSQL and reviewing other peoples patches during the latest couple of development cycles. With this addition, we will increase the capacity to handle the rising number of contributions we get, and get even more features into the upcoming versions of PostgreSQL.

Welcome to the team!

PostgreSQL Conference Europe 2013 - dates and location

nospam@example.com (Magnus Hagander) — Tue, 02 Apr 2013 15:17:00 +0000

Finally we're ready with phase 1 of the planning of PostgreSQL Conference Europe 2013.

PGConf.EU 2013 will be held on Oct 29-Nov 1, in at the Conrad Hotel in downtown Dublin, Ireland.

The format will be the same as previous years - one day of training before the main event consisting of three days fully packed with sessions about PostgreSQL.

We are hard at work to get our sponsorship campaign ready and intend to launch it within April. Also we are working on the call for papers and opening the registrations. So, please stay tuned and think about possible talk submissions!

So for now - mark your calendars, and start planning your trip! Follow the RSS feed from the website or our twitter feed for updates as soon as we know more details!

We look forward to seeing you this autumn on the Emerald Isle.

About security updates and repository "lockdown"

nospam@example.com (Magnus Hagander) — Fri, 29 Mar 2013 16:58:00 +0000

I have received a lot of questions since the announcement that we are temporarily shutting down the anonymous git mirror and commit messages. And we're also seeing quite a lot of media coverage.

Let me start by clarifying exactly what we're doing:

We are shutting down the mirror from our upstream git to our anonymous mirror
This also, indirectly, shuts down the mirror to github
We're temporarily placing a hold on all commit messages

There has been some speculation in that we are going to shut down all list traffic for a few days - that is completely wrong. All other channels in the project will operate just as usual. This of course also includes all developers working on separate git repositories (such as a personal fork on github).

We are also not shutting down the repositories themselves. They will remain open, with the same content as today (including patches applied between now and Monday), they will just be frozen in time for a few days.

Why?

So why are we doing this? It's pretty simple - it takes a few days to prepare packages for all our supported platforms, to do testing on these, and get them ready for release. If we just committed the security fixes and then proceeded with the packaging, that would mean that anybody who was following our repository would be able to see those fixes a few days before the fixes were available to the majority of the users. That also means that anybody looking for the flaw would get a few days of time when the full details of the bug was in the open (since the fix was applied in public), but yet all the installations around the world would be unpatched and left wide open for exploit.

By restricting access to view the patches until release time, we close this window. Yes, the vulnerability is still in the code that is out there today. But it has been in there for a few years, and nobody (that we know of) found it in that time. Hopefully, nobody will between now and release time. But by not explicitly showing the bug, we're at least keeping that risk as low as possible while still being able to warn our users that they will need to apply the patch as soon as it's out.

We do realize that this will make some people look harder at the PostgreSQL code over the next couple of days trying to find this bug, and write an exploit for it.

But you're using git?

I've seen a couple of comments along the line of "isn't this where you should be using a DVCS like git you're using, letting the people building the security fixes do that in a separate repository and merge it once ready, not needing to shut down the central one".

Turns out that is actually exactly what we are doing. The security fixes are mostly already developed, and as such are sitting somewhere else from the main repository. But we need at some point to merge these into the main repository, in order to let people build the packages. We only close down the repository mirroring right before this merge is done, and until the packages are ready to be released. It's not the work to develop the patch that requires the shutdown of the mirroring, it's the work to build and release packages.

The other advantage of the fact that we are using a DVCS, is that development does not stop during this time. Anybody working on a patch can keep working on it in their local copy of the repository. It's only the merge ("apply") of the patch to the upstream master branch that's going to be delayed. And that affects a much smaller group of people. Of course, it is a bit of an extra annoyance since we are currently trying to close out the open patches for the next release, but it's not a huge difference for most developers.

Are you going to publish the fixes eventually?

Yes, absolutely! We are not going to permanently hide any information, or try to obfuscate the contents of security patches (*cough*unlike some other players in the field).

Once the new versions are released, the git mirroring will resume. This will immediately mirror all the individual commits, including detailed commit messages showing what the bugs were (and of course including the fix itself). And we are assigning public CVE numbers to all security related bugs. At this point, the commit messages held in the queue will also be released, and appear on the pgsql-committers list for anybody who wants to read up on them. And of course, complete tarballs with the full release will be made available alongside the binary packages.

Bottom line

It's a difficult balance between keeping things open so that everybody can verify what's going on, and keep exploit information out of the hands of the bad guys. Our goal with what we did this time is to minimize exposure to our users for a potentially very bad exploit (depends on the scenario for each individual install, of course), while we work with downstream distributions to make sure our fixes can reach the users as quickly as possible.

Is it the right way? We don't know. It's the first time we do this, and it's not something we plan to do as a general process. We'll of course have to evaluate whether it was successful once it's all done.

Finally, for those of you who are our users, a short repeat. A new release is planned next week, current schedule is release on April 4th. We advise all users to review the security announcement and apply the fix as quickly as possible if the vulnerability is targetable in your environment. The patch will require installation of new binaries and a restart of the database, but no further migration work than that.

We take the security of our users seriously, and try our best to protect them as much as possible. It's out belief that the tradoffs we've done here are in their best interest. The future will tell, of course, if that belief is correct.

Out with the old... PostgreSQL 8.3 reaches end-of-life.

nospam@example.com (Magnus Hagander) — Thu, 07 Feb 2013 12:31:00 +0000

As of todays release announcement, PostgreSQL 8.3 is no longer a supported version. PostgreSQL 8.3 is end of life in according with the 5-year support policy for all PostgreSQL major versions.

If you are running on any of the 8.3 versions, you should upgrade your system to 8.4 or newer immediately. If this cannot be done immediately, you should at least upgrade to 8.3.23 in the meantime. When upgrading, it's likely worth upgrading to 9.2 or at least 9.1, and not just to 8.4 which only has a bit over a year before it also goes end of life.

Any users of PostgreSQL 9.2, 9.1, 9.0 or 8.4 should still look at upgrading their systems to the latest minor release as of todays updates, since they contain both security and stability fixes. Minor version upgrades are, as always, just a matter of replacing your binaries and restarting the database. Automatic updates should also be out on the yum and apt repositories shortly.

PGConf.EU 2012 - what about next year

nospam@example.com (Magnus Hagander) — Mon, 10 Dec 2012 16:45:00 +0000

In our conference feedback, we also asked for a number of things that are designed to help us decide what to do next year. The most obvious one, of course, being where the conference should be next year.

Without even reading the texts behind the truncated texts, it's obvious that this didn't help us very much. The only city that scored higher than the average was Vienna - and it's likely not a coincidence that this is the option that's geographically closest to Prague, so it's the closest one for most of our attendees. In general we have to declare this as a draw - all suggested cities seem to be equally popular. So no direct guidance from there. While we have nothing to announce yet, we have already started considering locations for next year - but it won't be announced until we know something for certain.

Continue reading "PGConf.EU 2012 - what about next year"

PGConf.eu 2012 - evaluation and summary

nospam@example.com (Magnus Hagander) — Sun, 09 Dec 2012 16:12:00 +0000

It's been a bit longer than last time, but the time has come for a blog post that sums up the feedback we received for this years pgconf.eu in Prague. Let's start out with the overall impressions:

We've actually managed to improve the over all impression slightly - last year had 51% giving us a rating of 5, and this year we got 57%. Event more important, we've kept the rate of people who are giving a score of 3 or lower at less than 1%. According to the numbers, the programme quality was slightly worse than last year - down to 65% giving it a rating of 5, vs 71% last year. We still have 97% giving 4 or 5 - up from 95% last year (well within the margin of error).

All in all, I think we can safely say "job well done" to all the people who worked on the conference - thanks for all your efforts!

Continue reading "PGConf.eu 2012 - evaluation and summary"

PGConf.EU 2012 - schedule is up! Early bird ends in less than a week!

nospam@example.com (Magnus Hagander) — Mon, 03 Sep 2012 14:30:00 +0000

We've finally finished the schedule for PGConf.EU 2012 in Prague in October, and put it up at http://2012.pgconf.eu/schedule/. Of course, a schedule is never truly finished - we will update it if necessary as we get closer to the conference. But the basics are there.

Our Opening Keynote this year will be delivered by industry veteran Joe Celko, who will give us an interesting look at the past and the future of SQL and other database technologies. And if you read and enjoyed the SQL for Smarties book from Joe, you have a chance to attend a full day of training with him as well! Limited number of seats available, so register quickly!

Other than Joe, we have many great talks from well known speakers in the PostgreSQL community such as Simon Riggs, Bruce Momjian, Josh Berkus, Dimitri Fontaine and Devrim Gunduz, as well as a number of new speakers with exciting stories to tell! There is more than enough content for everybody!

Early Bird registration for the conference ends this Friday. This is your last chance to register at the reduced price, after which the full price will be charged.

We hope to see you all in Prague this October!

PostgreSQL Conference Europe Trainings announced and Registration open

nospam@example.com (Magnus Hagander) — Mon, 02 Jul 2012 20:11:00 +0000

PostgreSQL Conference Europe 2012 in Prague, The Czech Republic, on October 23-26 is now accepting registrations for conference attendance at http://2012.pgconf.eu/registration/.

The Early Bird special price will be available until September 7th, but that's no reason not to get your registration in early! Should you for some reason want to register for just a part of the conference, single day rates are also available at this time.

We have also announced our training schedule at http://2012.pgconf.eu/training/. Take your chance to attend training on the day before the conference, held by major PostgreSQL community contributors like Bruce Momjian, Simon Riggs and Greg Smith, and relational database veteran Joe Celko. Local PostgreSQL experts Tomas Vondra and Pavel Stehule will also give training in Czech. These trainings have a limited number of seats available, so register early to ensure there is one for you!

And don't forget - the call for papers is still open! If you have already submitted a talk, or are planning to submit one, we suggest you wait to register until you have received a confirmation if the talk was accepted or not. The early bird rate will be available long enough for you to register after you have received this notification - and if your talk is accepted, attendance is of course free!

As usual, if you have any questions, don't hesitate to contact us at contact@pgconf.eu.

www.postgresql.org now active over IPV6 by default

nospam@example.com (Magnus Hagander) — Fri, 25 May 2012 20:01:22 +0000

For those of you who have been trolling our DNS details, you know that www.postgresql.org has been available over IPV6 for a while - just not activated in DNS. As of 15 minutes ago or so, we have now activated IPV6 for the main DNS record www.postgresql.org. Just like the IPV4 version, we are distributing the load across multiple frontends, with DNS based failover in case one of them were to go down. Right now, we have two IPV6 capable frontends and three IPV4 capable ones (the difference comes from our infrastructure in Europe being 100% IPV6 enabled, and our infrastructure in the US being 0% IPV6 enabled due to lack of upstream availability).

So if you are experiencing connectivity issues that started recently, check your IP stack if you are perhaps trying to connect over a broken IPV6 connection. If you need assistance beyond that, you can usually find helpful people in #postgresql on FreeNode who can help you figure out if the problem is on your end or ours!

Hopefully, this should be a completely invisible event to all our visitors....

Call for Papers - PostgreSQL Conference Europe 2012

nospam@example.com (Magnus Hagander) — Tue, 15 May 2012 19:49:00 +0000

The call for papers for PostgreSQL Conference Europe 2012 in Prague, the Czech Republic has now been posted. As usual, we are looking for talks on all topics related to PostgreSQL. At this point, we are looking for submissions for regular conference sessions - we will post a separate call for papers for lightning talks at a later time.

We are also still looking for sponsors - please see our website for details about the sponsor benefits and the costs.

Follow the news feed on our site, or our Twitter feed, for news updates!

PGConf.EU 2012 - announcement and call for sponsors

nospam@example.com (Magnus Hagander) — Tue, 27 Mar 2012 14:59:00 +0000

It's time to mark your calendars - PostgreSQL Conference Europe 2012 will be held at the Corinthia Hotel in Prague, the Czech Republic, on October 23-26 2012. As previous years there will be one day of professional training (Tuesday 23rd) and then three days of regular talks.

At this point, we are also opening our sponsorship program. We are looking for sponsors at all levels, from Bronze to Platinum. Please see our website for details about the sponsor benefits and the costs.

Follow the news feed on our site, or our Twitter feed, for further information as we finalize details.

Finding gaps in partitioned sequences

nospam@example.com (Magnus Hagander) — Fri, 27 Jan 2012 16:53:52 +0000

There are an almost unlimited number of articles on the web about how to find gaps in sequences in SQL. And it doesn't have to be very hard. Doing it in a "partitioned sequence" makes it a bit harder, but still not very hard. But when I turned to a window aggregate to do that, I was immediately told "hey, that's a good example of a window aggregate to solve your daily chores, you should blog about that". So here we go - yet another example of finding a gap in a sequence using SQL.

I have a database that is very simply structured - it's got a primary key made out of (groupid, year, month, seq), all integers. On top of that it has a couple of largish text fields and an fti field for full text search. (Initiated people will know right away which database this is). The sequence in the seq column resets to zero for each combination of (groupid, year, month). And I wanted to find out where there were gaps in it, and how big they were, to debug the tool that wrote the data into the database. This is really easy with a window aggregate:


SELECT * FROM (
   SELECT
      groupid,
      year,
      month,
      seq, 
      seq-lag(seq,1) OVER (PARTITION BY groupid, year, month ORDER BY seq) AS gap FROM mytable
) AS t
WHERE NOT (t.gap=1)
ORDER BY groupid, year, month, seq

One advantage to using a window aggregate for this is that we actually get the whole row back, and not just the primary key - so it's easy enough to include all the data you need to figure something out.

What about performance? I don't really have a big database to test this on, so I can't say for sure. It's going to be a sequential scan, since I look at the whole table,and not just parts of it. It takes about 4 seconds to run over a table of about a million rows, 2.7Gb, on a modest VM with no actual I/O capacity to speak of and a very limited amount of memory, returning about 100 rows. It's certainly by far fast enough for me in this case.

And as a bonus, it found me two bugs in the loading script and at least one bug in somebody elses code that I'm now waiting on to get fixed...

www.postgresql.org - brand new, yet old and familiar

nospam@example.com (Magnus Hagander) — Wed, 21 Dec 2011 13:33:00 +0000

Most of the visitors to www.postgresql.org probably never noticed that a couple of weeks back, the entire site was replaced with a new one. In fact, we didn't just change the website - just days before, we made large changes to our ftp network as well (more about that in another post, from me or others). So in fact, we hope that most people didn't notice. The changes were mainly a technical refresh, and there hasn't been much change to the contents at all yet. We did sneak in a few content changes as well, that have been requested for a while, so I'm going to start with listing those:

The developer version of the documentation (updated serveral times per day from the tip of the HEAD branch that will eventually become the next version of PostgreSQL) now live on the main website, and will use the same stylesheets to look a lot nicer than before.
Anybody who submits content to our site (news, events, professional services, products, etc) will notice there is now a new concept of an Organisation. This means that it will finally be possible to have more than one person manage the submissions from a single company or group.
Again for those that submit content, it is now possible to view which of your submissions are still in the moderation queue, and it's also possible to edit something after it's been submitted. In fact, you can edit your items even after they've been approved. Any such editing will be post-moderated, and if this is abused that organization will be banned from post-moderation - but we don't expect that to ever be necessary.
And finally, for those that submit content again, we've switched to markdown to format your submissions, instead of a very random subset of allowed HTML tags.

The rest of the changes are under the hood, and it's mostly done for two reasons:

The technology powering the site was simply very old
The frameworks used were quite obscure, which severely limited the number of people who could (or wanted to) work with them

Hopefully these two changes will make it easier to contribute to the website, so if you're potentially interested in doing that, please read on!

Continue reading "www.postgresql.org - brand new, yet old and familiar"

PGConf.EU 2011 - the speakers and the presentations

nospam@example.com (Magnus Hagander) — Sun, 06 Nov 2011 16:36:00 +0000

This part of the feedback is almost turning into a repost year from year. But it's a good thing to be reposting if any, so I'm doing it anyway. To start with, just take a look at these graphs:

Those are pretty fantastic ratings. A full 84% rated the content quality as 4 or 5, and only 1% rated it as less than 3. That basically comes down to there being no talks of bad quality. This confirms the feeling that we had when we tried to pick out the talks for this year - the number of great submissions where just huge. We had to reject around half the talks submitted, and there were only a few of those that we rejected because we thought they weren't very good. Most were simply rejected because we didn't have the time and space to accept them all.

The ratings people have given our speakers confirm what we have always thought to be one of the reasons people like the conference - and many other PostgreSQL conferences as well: you get to listen to and talk to the people who really know what they are talking about. Often because they are the very people who wrote the software in question. A whole 96% of all the ratings gave our speakers a score of 4 or 5 for their knowledge of the topic. And nobody scored lower than 3. These truly are the experts you get to meet!

Most of our speakers also scored very high on the Speaker Quality metric. Our top speakers this year were:

Speaker	Rating	Vote count	Standard deviation
Bruce Momjian	4.8	31	0.4
Ram Mohan	4.7	36	0.5
Selena Deckelmann	4.7	38	0.5
Magnus Hagander	4.6	52	0.6
Simon Riggs	4.6	43	0.6
Stephen Frost	4.6	18	0.5
Peter van Hardenberg	4.5	11	0.7
Gavin M. Roy	4.5	10	0.5
Greg Smith	4.5	68	0.7
Harald Armin Massa	4.4	10	0.5
Steve Singer	4.4	10	0.7
Gianni Ciolli	4.4	32	0.8
Dave Page	4.3	25	0.8
Heikki Linnakangas	4.3	12	0.9
Ed Boyajian	4.2	13	1.0
Marc Balmer	4.1	12	0.7
Dimitri Fontaine	4	11	0.8

This really is the reason why people come to the conference, and keep coming back the next year - our outstanding speakers! Thank you all for showing up this year to give your presentations, and we hope to see you again next year!

That concludes the posts I'm going to make about pgconf.eu feedback this year. Some of you have already asked about next year, and I'm not going to post any information about the feedback we got there - yet. We are reviewing the feedback we received, and are soon going to start looking for a good venue for next year. We have made the mistake before of announcing a location before we had a venue secured, and we're not going to do that again. We are going to announce it as soon as we know, but that will not be until we have actually decided on an exact venue. But we are absolutely planning to do it again next year, and sometime around the same time of the year. Exactly where we don't know yet...

PGConf.EU 2011 - the feedback is in

nospam@example.com (Magnus Hagander) — Fri, 04 Nov 2011 09:53:00 +0000

Almost exactly a week later than what we said, I have finally closed down the feedback system for PostgreSQL Conference Europe 2011. I think we all needed slightly more time than we expected to recover and catch up properly...

The detailed feedback for each speaker will be sent out during the day today, unless we run into any unforeseen technical issues, and I will try to summarize the conference-wide feedback here. If any particular note that you posted is not referred here, don't worry - we read them all, but there are far too many of them to post here.

Starting with the conference organization itself and it's venue, I'm really happy to see that we have managed to deliver something that the majority of our attendees really like:

Not a single vote less than 4, on a scale of 1-5, for the overall impression. And only one below 4 for the programme. I can only say a huge thanks to the big group of volunteers who ran this conference, and made it what it was. Clearly you did a good job!

Continue reading "PGConf.EU 2011 - the feedback is in"