PgCon 2011: PL/Summit notes

Thank you all who participated in the PL/Summit. We spent about 5 hours together for discussions about PLs, and then a few more hours at the pub.

Also, thank you to Dan who supported the meeting and provided pizza. And thanks to SPI for funding travel for several hackers that could not have made it to PgCon otherwise.

The rough notes from the meeting are at:

http://wiki.postgresql.org/wiki/PgCon_2011_PL_Summit
http://wiki.postgresql.org/wiki/PL_Features_Matrix

I put a few TODOs out there based on discussions, and for a reminder to myself to follow up with folks that had good ideas.

Here’s the TODOs:
* Publish code for PL/NULL – Tim
* Make a list of all the boilerplate code – Selena will send email to -hackers about it
* Set up a 1/2 day meeting at the next pgcon – Josh Berkus
* Set up a Skype session in a few months time to go over progress – Selena (Tim, Alvaro agreed to attend)
* Further discussion of DDL triggers – Jan Wieck
* Further discussion of permission system – Steve Singer
* Further discussion of per-statement config parameters for things like timezone – Jan Urbanski

PgCon Pub Track: Learning more about Synchronous Replication

So, we’re at the Pub and doing “create a billion tables” time trials with Jan Urbanski using Python and Josh Berkus using Perl.

We’re also hacking on a test framework the Slony developers have, specifically hacking with Steve Singer. What we discovered is that sync rep doesn’t wait for a WAL segment to be *replayed* before it returns. In the pg_stat_replication table, we see sent_location, write_location and flush_location synchronized, but not replay_location.

This makes sense from a database perspective, but may be surprising behavior for application developers. There are patches out there (according to what I just heard from Bernd) to make synchronous replication wait for replay on the slave, but it’s not certain when that will be committed. It definitely won’t be part of version 9.1.

I just wrote up configuration details from a database administrator’s perspective, and am planning on doing some additional work to make a highly condensed configuration tutorial for our main docs. We definitely need to explain this more clearly for users, who might be thinking of it more from an application perspective.

At PgCon 2011 – day 0

I wrote my review of synchronous replication over on Emma’s Tech blog (It’ll probably be published mid-day Tuesday). I’m visiting Ottawa this year on behalf of Emma, one of many great sponsors of Postgres’ yearly international developer conference, pgCon.

This week will be packed for me – attending the Clustering summit, the developers meeting, presenting about Emma’s database systems, leading the lightning talks, and of course attending the many parties this week.

Because we are spread so far around the globe, pgCon is often our one chance to get together and really dig into problems in-person.

And, I’m pulling together our first ever Procedural Language summit. With the new extension system, over 30 procedural languages implemented, and a ton of new features being added to existing PLs, I thought it was time PL developers should come together and have a chat. I’ve still got a few details to work out before Saturday (sorry all that RSVP’d – final agenda coming soon!).

I’m hoping to also have another, unrelated, announcement this Wednesday. Hopefully all the details come together!

Anyway, with that cliffhanger, I’m off to get a good night’s rest before the clustering summit tomorrow.

PL Developer Summit at PgCon, May 21!

UPDATE: We have 18 PLs. Added to the list from comments. :)

You’re probably aware that PostgreSQL supports a few procedural languages, PL/PgSQL being the most well-known for compatibility with Oracle’s PL/SQL.

Interest in PostgreSQL Procedural Languages (PLs) has grown significantly in the last few years and so PgCon is hosting a special PL summit on Saturday May 21, 2011.

Did you know that there are 17 other procedural languages are currently implemented?

  1. PL/Tcl and PL/Tclu
  2. PL/Perl and PL/Perlu
  3. PL/Python and PL/Pythonu
  4. PL/Ruby
  5. PL/Java
  6. PL/Lua
  7. PL/LOLCODE
  8. PL/Js
  9. PL/Proxy
  10. PL/PHP
  11. PL/sh
  12. PL/R
  13. PL/Parrot
  14. PL/scheme
  15. PL/Perl6
  16. PL/PSM
  17. PL/XSLT

And we have at least one proprietary PL from EnterpriseDB:

We invite PL developers, PostgreSQL core hackers, those interested in future PL development and PgCon attendees interested in learning more to attend!

Before we decided to create this summit, I put together a survey for PL developers. All survey respondents wanted a summit to happen!

The most popular topics were:

  • Postgres PL Interface Improvements
  • Connecting with other PL developers
  • New features in PLs
  • Hacking together
  • State of PLs
  • Distributions and builds
  • PG9.1 extensions vs PL languages
  • Security (pl vs plu)
  • PGXN

The most popular PLs were:

  • PL/PgSQL
  • PL/Perl
  • PL/Python
  • PL/R

The summit is open to attendees of PgCon and special guests. Please RSVP and help set the agenda.

The agenda and any results of the summit will be published on the wiki.

PgCon 2010 – PL/Parrot, Simulated Annealing, Exclusion Constraints, Postgres-XC

PgCon this year was full of bold ideas, delivered in the quiet manner typical of the Postgres community. Talks by Jonathan Leto, Jan Urbanski and Jeff Davis all presented new features and ideas that show there is so much room yet in Postgres as a project to contribute, and innovate. I was also delighted to see Postgres-XC (touted as a “Postgres RAC”) release code, and give a great presentation on the high-level details.

Jonathan Leto presented work on PL/Parrot, along with David Fetter. Parrot is a dynamic language virtual machine, allowing implementation of multiple dynamic languages which can then share classes (from the docs: “In theory, you will be able to write a class in Perl, subclass it in Python and then instantiate and use that subclass in a Tcl program.”). The project is to embed Parrot in PostgreSQL, and eventually, implement dynamic languages inside the virtual machine. Advantages to doing this are that it will make implementing new dynamic languages in Postgres much easier, because the language implementers won’t have to learn the PL interface. Another useful feature in PL/Parrot is the implementation of a security opcode in Parrot which essentially controls access to open(), a key to implementing a secure procedural language in Postgres. (I’m sure Jonathan will correct me if I didn’t describe this properly :D )

Jan Urbanski gave a talk on join ordering via Simulated Annealing, called Replacing GEQO. The approach was pretty interesting, involved math that required me to scratch my head a bit, and the initial performance improvements for many-join queries made it seem appealing. The original -hackers posting from Jan, includes a few hairy queries from Andres Freund which confound the GEQO referenced later in the thread. Jan’s posted the code, and I’m looking forward to seeing how it develops this year.

Jeff Davis presented exclusion constraints, which are part of 9.0. He is continuing his work on temporal data types with a clever and very useful generalization of UNIQUE. UNIQUE constrains equality, while exclusion constraints allow other operators (in the most cited example, Jeff demonstrates “overlaps” in the PERIOD datatype).

Postgres-XC was officially presented and released. For efforts in Postgres clustering, releasing the code is a huge step forward toward mainstreaming work in the community on clustering. This release solidifies community work that started last year, with NTT and the support of the Japanese PostgreSQL User Group in having a clustering summit back in November 2009.

I was disappointed to miss a few talks (like hypothetical indexes, pg_statsinfo, CB’s pgMQ) but looking forward to hearing the recordings as they are published!

PgCon Day 1: Developer meeting

Jan, talking about DDL triggers

I’m back from the developer meeting where we blasted through eight hours of presentations and discussions from key members of the PostgreSQL developer community.

One great outcome from the meeting is that WE ARE MOVING TO GIT.

There’s lots of other great stuff on that wiki notes – like a list of possible features for 9.1, plans for DDL triggers, and a continued discussion of snapshot cloning.

There was also much approval voiced for the alpha process that Peter Eisentraut championed last year.

I volunteered to steer our next “reviewfest” – kicking off June 15! And I’m trying to rope another developer into the next commitfest, which we anticipate starting on July 15. The plan is to stick with our development schedule from last year.

And I’m working with Josh Berkus to put together an open issues list for 9.0. I think I went a bit overboard, and many of the things I listed are probably resolved – hopefully committers will make quick work of the list, and we’ll be speeding along toward releasing 9.0 in June!

I missed out on the group picture, so I look forward to someone’s photo editing efforts in the near future. :)

What are you waiting for? Get your PgCon talks in now!


Yes, that’s me, with Tom Lane. You, too, might be able to get your picture with Tom!

Like Josh Berkus said yesterday:

As of today, you have 2 weeks left to submit talk proposals to PGCon.

You know you want to. PGCon is the international conference for PostgreSQL hackers, sysadmins, application developers, SQL geeks and other Smart People. Submit your talk! Be a Smart Person too!

PGCon will be happening May 21-22 in Ottawa, Canada, with tutorials on May 19 and 20. Some financial help is often available for speakers, but none is available for non-speakers. So submit, submit!

We particularly could use some talks on the new 8.4 features, really creative PostgreSQL applications, massive Postgres scaling, PostGIS, BioPostgres, and a few case studies. This means you.

I attended PgCon last year for the first time. Not only were the presentations top notch, but Dan Langille‘s hospitality set the groundwork for yet another fantastic community-building experience PostgreSQL community members experienced during the 2006 Anniversary summit in Toronto, again in 2007 at the first PgCon.

We had plenty of outstanding socializing and hacking opportunities. Last year’s conference started with a gathering of committers that was fodder for great pub and hallway track conversation all week. Great talks I saw included Andrew Sullivan’s Idle thoughts on PostgreSQL Project Management, Greg Sabino Mullane’s Bucardo talk about this multi-master replication tool, and Magnus Hagander’s walk through how search.postgresql.org was implemented.

Ottawa was beautiful last year, and I can’t wait to go back this May!

PgCon 2008 – big announcements, community conversations

PgCon was a very exciting conference, with a lot of people from Europe, Asia, Australia and South America traveling to be part of it. I read that 175 people attended, and based on how crowded both parties were, it’s not hard to believe!

The biggest announcement for me was that the PostgreSQL Europe is finally a non-profit organization! I made a slide for my lightning talk with Magnus, Gabriele, Jean-Paul and Andreas on it:

PgEU announced

I got a ton of great feedback about the User Groupalooza slides. I also enjoyed meeting Jean-Paul Argudo, a fellow Drupaler.

There were a bunch of community-focused conversations, some focused inward on developers, some looking out to the rest of the world from inside Pg, and others from the outside looking in:

All the talks were recorded, so I look forward to listening to them again – without the distraction of Twitter! :)

A couple talks I thought were really great for web developers were:

  • Magnus Hagander’s search.postgresql.org talk gave some great examples and code showing how to use PostgreSQL’s tsearch capabilities with a PHP-based website.
  • Clark Evan’s talk on HTSQL, a REST-ful web inteface application. It looks pretty cool and I’m interested in trying it out. They are using it for medical records report generation and have even given the ability to generate queries to the end users.

Thanks so much to Dan who got me to the conference this year! I learned a lot, and really enjoyed meeting so many people that I’ve only communicated with over email for the past three years.

PgCon Lightning talk: User Groupalooza

I gave a Lightning talk today about PostgreSQL User Groups. I wasn’t able to get through ALL my slides – but I only had to rush through the last three. (click on the cat below to download – 5MB)

splash for user group talk

Lightning talks are some of my favorite sessions. I got to announce the incorporation of PostgreSQL-EU! We had a talk about DBIx::Cache (which you should all check out!), a cool open source lab in Japan that Hiroshi Saito works for (only for Japanese, but very cool), Gavin Roy talked about Staplr and a new benchmarking tool called Playr that was just released on Thursday, and six more talks! We hope to publish the rest of the slides shortly.

At PgCon in Ottawa, Day 1

Oops, this got published early. I’ll be updating this shortly.. probably over lunch.

Magnus posted some photos already!

Summary of last night:

We had a great time last night at the pub for registration, and then over to the Yahoo! party. We had fun talking databases, beer, and the difficulty in trying to understand Australian accents. I learned from Tom (not Tom Lane) that Adelaide, Australia has a fantastic wine-growing region. Alvarro once again recommended that Jeff commit his time interval data type. I think Jeff was convinced to do this for 8.4. Now I have to update the documentation!