Magnus Hagander's PostgreSQL blog

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"

Stockholm PUG finally off the ground

nospam@example.com (Magnus Hagander) — Wed, 05 Oct 2011 08:54:00 +0000

Last night, we finally got a PostgreSQL User Group in Stockholm started. We've discussed this for years, but never got around to making it actually happen. Well, with big thanks to Claes who took care of the main organization tasks, we finally did - and I'll happily declare it a big success. It was our first meeting, and we actually didn't promote it very well (so bad that at least one fairly well-connected PostgreSQL community guy didn't realize it was on until registration was already closed - I'm sure others missed it too), and we still managed to get more than 30 people there! Awesome!

Hopefully we can keep the numbers at this level. For now, we are planning to meet around once every three months or so, which means we'll be looking at the next meeting sometime in January. Exact date, and also location, yet to be decided upon.

Claes is supposed to be setting us up with a website (we have plenty of domains already...) and an associated mailinglist, and I guess a registered IRC channel as well. Hopefully soon. But given that he set us up with a room, a projector, pizza and beer last night (thanks, btw, and thanks to Glue for picking up the bill), I think we can give him a couple of hours before we start complaining...

So - see you at the next Stockholm PUG meeting!

pgconf.eu schedule & keynote announced

nospam@example.com (Magnus Hagander) — Mon, 19 Sep 2011 16:57:00 +0000

A little bit later than we hoped, we have now finally published the schedule for pgconf.eu. Three days full of presentations to choose from - and of course also the always popular lightning talk sessions. The schedule listed now is what we consider the final version, but we obviously reserve the right to make last-minute modifications both to which talks are included and exactly when they are scheduled, if necessary.

Keynote speaker

We are also happy to announce that the conference keynote will be presented by by Ram Mohan, CTO of Afilias, who will be talking about how Afailias has built their company on open source solutions, and how this has turned into a great success. Afilias as a company has been deeply involved with PostgreSQL for a long time, including employing former Core Team member Jan Wieck and leading the development of the Slony replication system.

pgconf.eu training announced, call for papers deadline extended

nospam@example.com (Magnus Hagander) — Sun, 21 Aug 2011 20:45:00 +0000

Training

We are happy to announce that our training schedule is now available at http://2011.pgconf.eu/training/. These trainings are full or half day sessions on the day before the regular conference sessions, and come at an extra cost. The available trainings are:

Performance From Start to Crash by Greg Smith, 2ndQuadrant
Mastering PostgreSQL Administration by Bruce Momjian, EnterpriseDB
Building business applications for Cloud with Servoy by Robert Ivens, ROCLASI
Slony, a still useful replication tool by Guillaume Lelarge, Dalibo

Seats are limited at these trainings, so we advise you to book as soon as possible. Training is booked as additional options on the standard conference registration form.

Call for papers

Since we are still in vacation period for a lot of people, we have decided to extend the deadline for our call for papers. The new deadline for submitting talks is midnight, Sep 2nd.

We will, however, start approving talks that have already been submitted as soon as possible, and announce them as soon as we have decided. That means that if you want to be sure that we will have time to review your talk, you should submit as soon as possible!

Full call for paper details are available on the site.

Get your talks in for pgconf.eu 2011

nospam@example.com (Magnus Hagander) — Tue, 16 Aug 2011 14:13:00 +0000

The call for papers for PGConf.EU 2011 in Amsterdam will close at the end of this week. Now is the time to get your talk submissions in!

We are interested in all kinds of talks - from deep technical ones, to novice oriented advise and case studies of interesting things done with PostgreSQL. We expect a wide range of different skillsets amongst our visitors, so we want a good spread of the talk topics as well!

Of course, all speakers get free entrance to the conference on all days (training sessions not included).

If you have any questions for us, don't hesitate to contact us.

So, there is nothing to wait for. Head over to the call for papers site and submit your ideas! And please help us spread the word to potential speakers in other communities as well, who may not have seen our posts yet!

PGConf.EU open for registration!

nospam@example.com (Magnus Hagander) — Thu, 14 Jul 2011 21:23:00 +0000

PostgreSQL Conference Europe is now accepting registrations for conference attendance.

The Early Bird special price will be available until September 5th, 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.

If you are planning to attend one of our training sessions, the schedule has not yet been published for that, and it is therefor not yet possible to register for trainings. However, do not worry: the early bird rate will be available for all attendees who register for the trainings - all the way until right before the conference.

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 on 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.

Call for papers - PGConf.EU 2011

nospam@example.com (Magnus Hagander) — Mon, 30 May 2011 07:49:00 +0000

PostgreSQL Conference Europe 2011 will be held on Ocober 18-21 in the Casa 400 Hotel in Amsterdam, The Netherlands. It will cover topics for PostgreSQL users, developers and contributors, as well as decision and policy makers. For more information about the conference, please see the website at http://2011.pgconf.eu/.

We are now accepting proposals for talks. Please note that we are looking for talks in English, Dutch, German and French.

Each session will last 45 minutes, and may be on any topic related to PostgreSQL. Suggested topic areas include:

Developing applications for PostgreSQL
Administering large scale PostgreSQL installations
Case studies and/or success stories of PostgreSQL deployments
PostgreSQL tools and utilities
PostgreSQL hacking
Community & user groups
Tuning the server
Migrating from other systems
Scaling/replication
Benchmarking & hardware
PostgreSQL related products

Of course, we're happy to receive proposals for talks on other PostgreSQL related topics as well.

We also have a limited number of longer, 90-minute, slots available. Please indicate clearly in your submission if you wish to make a 90-minute talk.

Finally, there will be a session of five minute lightning talks. A separate call for proposals will be made for them further on.

The submission deadline is August 21st, 2011. Selected speakers will be notified before Sep 5th, 2011.

Please submit your proposals by going to http://2011.pgconf.eu/callforpapers and following the instructions.

If your proposal is in a non-english language, please include a single-sentence description of the presentation in English as well in the field for submission notes.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date. If your proposal has been accepted, you will be informed by email within two weeks of the submission deadline.

This call for papers is also available on the web at http://2011.pgconf.eu/callforpapers

We look forward to hearing from you, and seeing you in Amsterdam in October!

Extensions in PostgreSQL 9.1 fixes another pet-peeve

nospam@example.com (Magnus Hagander) — Mon, 16 May 2011 07:58:00 +0000

One thing I've really disliked is the fact that contrib modules had installation scripts that enforced the schema to public for the installation. In my opinion, for no useful reason at all.

For example, I often install the pgcrypto contrib module. And I install this in the pgcrypto schema, that I then either add to the search_path variable or just explicitly use in my queries, with things like pcrypto.crypt('foobar','barfoo'). For versions prior to 9.1, being able to do this required me to manually edit the installed pgcrypto.sql file, to remove the SET search_path = public; command.

Extensions in 9.1 makes this so much nicer. To get pgcrypto into it's own schema, I now just need to do:

postgres=# CREATE SCHEMA pgcrypto;
CREATE SCHEMA
postgres=# CREATE EXTENSION pgcrypto SCHEMA pgcrypto;
CREATE EXTENSION

If I happen to create it in public by mistake, I can even move it after the fact!

postgres=# ALTER EXTENSION pgcrypto SET SCHEMA pgcrypto;
ALTER EXTENSION

You still need to create the schema manually - in theory we could auto-create that, but the work is still a lot easier than before. And fully supported!

Remote log reading in PostgreSQL 9.1

nospam@example.com (Magnus Hagander) — Mon, 02 May 2011 19:05:00 +0000

PostgreSQL 9.1 beta1 now available - now is a great time to start testing it, and trying out all the great new features.

There have always been a number of ways to read your PostgreSQL logs remotely, over a libpq connection. For example, you can use the pg_read_file() function - which is what pgadmin does. PostgreSQL 9.1 adds a new and more convenient way (in some ways) to do this - using SQL/MED.

PostgreSQL 9.1 comes with SQL standard SQL/MED functionality. The MED in is short for "Managemend of External Data", and as the name sounds, it's about accessing data that's external to the PostgreSQL server. The SQL/MED functionality is not (yet) complete, but it's already very useful in it's current state.

In SQL/MED, there is something called a Foreign Data Wrapper, that can be compared to a driver. Using this FDW, we can create one or more Foreign Servers, which is a definition of how to connect to a specific instance of the service - if any. Finally, we can create one or more Foreign Tables on each of the Foreign Servers, giving us direct access to the remote data using SQL.

Continue reading "Remote log reading in PostgreSQL 9.1"

Joining the PostgreSQL Core Team

nospam@example.com (Magnus Hagander) — Wed, 27 Apr 2011 19:02:00 +0000

As has just been announced here, I was recently invited to join pgsql-core, and have accepted.

I guess the guys currently on it finally got tired of all my complaints, and figured out the way to make me stop was to suck me into the organization. The future will tell if their strategy will be successful or not...

For those who don't know (this hopefully doesn't include my readers on Planet PostgreSQL), pgsql-core is the "steering committee" for the PostgreSQL project. Exactly what they do seem to be somewhat up for debate both outside and inside of the group itself, but it at least has something to do with the leadership of the project...

Anyway, I'd like to thank the guys in the group for showing this trust in me, and shall do my best not to screw it up!