The First pgCon Unconference: Join Us!

We're having an Unconference Day at pgCon 2013!  As far as I know, this is a first for the PostgreSQL community*, so I've prepared some information on what the unconference is and how it happens. The Unconference Day is taking place on Saturday, May 25, after the main session days at pgCon, so I hope you bought your plane tickets to stay over Saturday!

For those of you who are unfamiliar, an Unconference is a participant-driven meeting. Typically at an unconference, the agenda is created by the attendees at the beginning of the meeting. Anyone who wants to initiate a discussion on a topic can claim a time and a space. Unconferences typically feature open discussions rather than having a single speaker at the front of the room giving a talk, although any format is permitted.

This means that we want you -- yes, you -- to lead a session at the Unconference Day.  Don't be shy!

pgCon is the primary venue for PostgreSQL contributors around the world to collaborate in person. As pgCon has grown, it has added additional ways to be involved in PostgreSQL development, including the Developer Meeting, the Cluster-Hackers Summit, the PostgresXC Summit, the Schemaverse Tournament, and many other satellite events.

This Unconference Day will permit us additional collaboration time, in order to work on many things which didn't make it into the formal pgCon program, including:

• open or round-table discussions among community teams, such as the Web Team, Advocacy Team, Buildfarm hosts, and others.
• new or emerging topics which came up after the pgCon Call for Papers was closed.
• spillover topics from things which came up during pgCon
• development planning and coordination for contributors who were not invited to the Developer Meeting
• active working or hacking sessions

Most of the Unconference Day will be planned between 10am and 11am on the day of.  However, there are a couple things you could do right now to help get ready for it:

1. If you have an idea for a session at the Unconference Day, add it to the wiki page.
2. If you are available to volunteer to help me run the day, please contact me.

I look forward to seeing you there!

(* Turns out, as usual, our Japanese collegues have beaten us to the punch.  There was an unconference in Tokyo last season.)

sorted_mode Aggregate Function

So a boolean mode() was very simple to construct, partly because booleans have only three potential values (TRUE, FALSE, NULL).  However, in many cases we want mode() for, say, integers.  Now, there are several constructions of mode() depending on what statistic you really want, but here I'm concerned with the simplest one: Most Common Value (MCV).

One way to get this kind of mode is to do a windowing function, but as mentioned this works poorly with other aggregates in the same result set.  So let's take the same custom aggregate approach and see if we can do better.

Now, for types other than boolean, mode() is a "two-pass aggregate".  That means it's impossible to calculate in one pass; you need two passes, one to sort the set, and one to count the items and pick the MCV.   Since we know we'll need two passes, we'll construct our aggregate to assume that it's receiving sorted data going in, and make sure it gets sorted data when we use it.  Given 9.2's new ORDER BY clause for aggregates, that's easy to ensure.

For a state type, we'll need a 4-part register.  This register will include:

1. the last seen value
2. the count of the last seen value
3. the most common value so far
4. the count of the most common value so far

Again, we'll use an array rather than a composite type so that we don't have to create a type for the function.  Then we can construct a pure-SQL state function:

create or replace function sorted_mode_state(
    tally bigint[], nextval bigint
)
returns bigint[]
language sql
immutable
as $f$
-- have we seen this value before?
SELECT CASE WHEN nextval = tally[1] THEN
    -- if weve seen it before, does it out-count the prior MCV?
    CASE WHEN tally[2] = tally[4] THEN
        -- if so, swap the MCV
        ARRAY[ nextval, tally[2] + 1, nextval, tally[2] + 1 
    ELSE
        -- if not, keep counting
        ARRAY[ nextval, tally[2] + 1, tally[3], tally[4] ]
    END
-- is the register uninitialized?  then take the first value
WHEN tally[1] IS NULL THEN
    ARRAY [ nextval, 1, nextval, 1 ]
-- skip nulls
WHEN nextval IS NULL THEN
    tally
ELSE
    -- if it's a new value, count 1
    ARRAY [ nextval, 1, tally[3], tally[4] ]
END;
$f$;







The final function just picks out tally[3], returning NULL if no MCV has been selected:

create function sorted_mode_final(
    tally bigint[]
)
returns bigint
language sql
immutable
as $f$
SELECT CASE 
-- do we have an MCV?
WHEN tally[4] > 1 THEN tally[3]
-- otherwise, do we have a single-value set?
WHEN tally[1] = tally[3] THEN tally[3]
-- is there no clear mcv?  return null
ELSE NULL END;
$f$;

Then we can build our aggregate:

CREATE AGGREGATE sorted_mode ( bigint ) (
    SFUNC = sorted_mode_state,
    STYPE = BIGINT[],
    FINALFUNC = sorted_mode_final,
    INITCOND = '{}'
);

Usage:

SELECT server, date_trunc('hour', poll_time) as thehour,
     sorted_mode(status ORDER BY status) as status_mode
FROM server_status
GROUP BY server, date_trunc('hour', poll_time)
ORDER BY date_trunc('hour', poll_time), server;

This approach has some inherent limitations, of course:

• if no value appears more than once, it returns NULL, unless there's only one value in the set;
• if several values appear the same number of times, it returns the first one in the sort order;
• it can't do estimated mode or other mode constructions.

However, it's "good enough" for a lot of applications which want the most common value for display.  And it's very fast compared with other approaches.

A very simple custom aggregate

Custom aggregates are one of those features which are unique to Postgres, and seem hopelessly obscure.  Once you actually create one in earnest though, you'll wonder how you ever lived without one.  To get you started, I'm going to walk you through creating a very simple custom aggregate: one which gives you the mode (most frequent value) for a boolean column.

Why would you want such a thing?  Well, imagine you're monitoring your webservers, and you want to present 1-hour summaries of whether they are up or down.  However, you have data for each 30 seconds.  For a webserver which is up most of the time you want to return TRUE; for one which was down for most of the hour you want to return FALSE.  If the monitoring system was down (and thus there's no data), you want to return NULL.

You could do this using windowing queries.  However, that doesn't work well with other cumulative statistics, such as the number of minutes up.  You want something you can display side-by-side with other aggregate stats.  Well, with PostgreSQL, it's surprisingly easy!

First, we need a "state function" which accumulates data about the boolean.  This state function generally has two parameters, a data type which accumulates value, and the data type of the column you're aggregating.  In our case, we want to accumulate two counters: a count of falses and a count of trues, which we do using an array of INT.  This can be done with a pure-SQL function:

CREATE OR REPLACE function mode_bool_state(int[], boolean)
returns int[]
language sql
as $f$
SELECT CASE $2
WHEN TRUE THEN
    array[ $1[1] + 1, $1[2] ]
WHEN FALSE THEN
    array[ $1[1], $1[2] + 1 ]
ELSE
    $1
END;
$f$;

Once both registers have been accumulated, we need to use a "final" function to compare them and decide which is the mode, which will accept the accumulation type (INT[]) and return boolean:

CREATE OR REPLACE FUNCTION mode_bool_final(INT[])
returns boolean
language sql
as $f$
SELECT CASE WHEN ( $1[1] = 0 AND $1[2] = 0 )
THEN NULL
ELSE $1[1] >= $1[2]
END;
$f$;

Then we can declare the aggregate to bring it all together:

create aggregate mode(boolean) (
    SFUNC = mode_bool_state,
    STYPE = INT[],
    FINALFUNC = mode_bool_final,
    INITCOND = '{0,0}'
);

SFUNC and FINALFUNC refer to our two functions.  STYPE tells Postgres what state accumulator type to use, and INITCOND initializes the INT[] so that it's not NULL to start.

Let's see if it works!

SELECT server_name,
    sum(CASE WHEN server_up THEN 0.5 ELSE 0 END) as minutes_up,
    mode(server_up) as mode
FROM servers 
WHERE montime BETWEEN '2013-04-01' and '2013-04-01 01:00:00';

server_name      minutes_up       mode
web1             56.5             TRUE
web2             0.0              FALSE
web3             48.0             TRUE
web4             11.5             FALSE

So easy you'll wonder why you didn't do it before!

(thanks to David Fetter for suggesting INT[] instead of a composite type)

Recap of last week's security release

Rather than writing up a blow-by-blow of last week's security release here, I've written a full article for LWN.   Subscription required, I'm afraid (or you can wait 2 weeks).

Now we find out how many Postgres servers there really are

Seven years ago, the MySQL worm spread throughout the internet by taking advantage of a critical vulnerability in MySQL authentication on Windows.  Before the worm, I hadn't really believed Marten Mickos' claims about the number of MySQL installations.  Once over 30,000 servers were infected, though, I believed him.

Well, given the vulnerability announced and patched today, I think we'll have a chance to find out how many PostgreSQL servers there are.  You have updated your servers, right?

In the PostgreSQL world, we're used to thinking in terms of a few thousand users because that's the number which is active in the community and that we hear from regularly.  It's likely that today's vulnerability will show us how many PostgreSQL users there really are.  For example, this security scanner finds over 120,000 PostgreSQL servers which are listening on port 5432 on public IPs (and if your server is on that list, you'd better patch it!).

No idea why 40% of these servers are in Poland.  I had no idea that Depesz was that busy.  A word to the wise, Depesz: firewalls?

PostgreSQL To Adopt New Version Numbering System

According to several focus groups, greatest among the marketing obstacles for PostgreSQL is our "old skool" three-digit version numbering system. As such, I am pleased as punch to announce that we have adopted a new, "hip" and "cutting-edge" version numbering system.

First, since decimal version numbers are antiquated, we will be adopting a system which uses only a whole integer for the version number.  In order to accommodate all of the minor update releases we do (another one is coming up on Thursday, so I hope you're planning to update!), each new major version of PostgreSQL will be a multiple of 25, starting with our next release, PostgreSQL 50.

Then the minor releases will each add on one version, so the first update to version 50 will be version 51, then 52, etc.  To know your core version of Postgres, simply divide by 25 and round down.  Any PostgreSQL annual release which requires more than 24 updates will be preemptively EOL'd in as too buggy to live.

Since file format compatibility is now a big issue, given pg_upgrade, each version will also carry a file format version, which will be alphabetical (ASCII alphabet).  When we break file format compatibility, you will know because the letter changes.  With these things taken together, our next release PostgreSQL will be:

PostgreSQL-C 50

The version after that will be:

PostgreSQL-C 75

Unless we break the file format, in which case it will be:

PostgreSQL-D 75

See how much more intuitive and user-friendly the new system is?

Now, we realize that this new numbering system may be somewhat confusing for users until they get used to it, and that not everyone likes numerical version numbers (at least, our focus group told us it was so).  Accordingly, we are only renumbering version 9.2 as version 25 (currently version 28, actually); prior versions of PostgreSQL will retain their existing numbering.

Further, to give users a second, even more friendly way to track PostgreSQL versions, we will be giving each release a "codename".  In keeping with PostgreSQL's international user base, these codenames will alternate languages, with each language community voting on the name they want to use.   Thus, the next version of PostgreSQL will be also called:

PostgreSQL Hilaria

Our Japanese community has chosen the name of the prior version of Postgres (currently known as 9.2.3), which will hereafter be:

 

PostgreSQL 化け狸 update 3

 

The PostgreSQL community will alternate between the numerical version number and the codename in our documentation and literature in order to get users comfortable with both systems. You can also track which version comes after which other version based on the Unicode UTF-8 ID of the first character of the codename modulo 100.  A built-in PostgreSQL function and data type will be supplied for easy sorting of version numbers.

I hope you're as excited about this as I am!  A brave, new, marketing-driven future awaits us!