Thursday, April 25, 2013

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!


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


  • spillover topics from things which came up during pgCon
  • 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.)

Wednesday, April 24, 2013

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

        -- if not, keep counting
        ARRAY[ nextval, tally[2] + 1, tally[3], tally[4] ]

-- is the register uninitialized?  then take the first value
    ARRAY [ nextval, 1, nextval, 1 ]

-- skip nulls

    -- if it's a new value, count 1
    ARRAY [ nextval, 1, tally[3], tally[4] ]

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
as $f$

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

Then we can build our aggregate:

CREATE AGGREGATE sorted_mode ( bigint ) (
    SFUNC = sorted_mode_state,
    FINALFUNC = sorted_mode_final,
    INITCOND = '{}'


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.

Wednesday, April 17, 2013

A very simple custom aggregate





CREATE OR REPLACE function mode_bool_state(int[], boolean)
returns int[]
language sql
as $f$


returns boolean
language sql
as $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}'


Let's see if it works!

SELECT server_name,
    mode(server_up) as mode
FROM servers 

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!


Tuesday, April 9, 2013

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

Thursday, April 4, 2013

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?

Monday, April 1, 2013

PostgreSQL To Adopt New Version Numbering System





PostgreSQL-C 50

The version after that will be:

PostgreSQL-C 75


PostgreSQL-D 75




PostgreSQL Hilaria



PostgreSQL 化け狸 update 3


