unsafe UPSERT using WITH clauses

By now you've read about PostgreSQL 9.5 and our shiny new UPSERT feature.  And you're thinking, "hey, 9.5 is still in alpha, how do I get me some UPSERT action now?"  While there are some suggestions for workarounds on the PostgreSQL wiki, I'm going to show you one more method for approximating UPSERT while you help us test 9.5 so that it will come out sooner.

The general suggested way for handling UPSERT situations is one of the following:

1. Try to do an INSERT.  On error, have the application retry with an UPDATE.
2. Write a PL/pgSQL procedure which does insert-then-update or update-then-insert.

Both of these approaches have the drawback of being very high overhead: the first involves multiple round-trips to the database and lots of errors in the log, and the second involves major subtransaction overhead.  Neither is concurrency-safe, but then the method I'm about to show you isn't either.  At least this method avoids a lot of the overhead, though.

What's the method?  Using writeable WITH clauses.  This feature, introduced in 9.1, allows you to do a multi-step write transaction as a single query.  For an example, let's construct a dummy table with a unique key on ID and a value column, then populate it:

     create table test_upsert ( id int not null primary key, 
        val text );
     insert into test_upsert select i, 'aaa'
       from generate_series (1, 100) as gs(i);

Now, let's say we wanted to update ID 50, or insert it if it doesn't exist.  We can do that like so:

    WITH
    newrow ( id, val ) as (
        VALUES ( 50::INT, 'bbb'::TEXT ) ),
    tryupdate as (
        UPDATE test_upsert SET val = newrow.val
        FROM newrow
        WHERE test_upsert.id = newrow.id
        RETURNING test_upsert.id
    )
    INSERT INTO test_upsert
    SELECT id, val
        FROM newrow
    WHERE id NOT IN ( SELECT id FROM tryupdate );

The above tries to update  ID=50.  If no rows are updated, it inserts them.  This also works for multiple rows:

    WITH
    newrow ( id, val ) as (
        VALUES ( 75::INT, 'ccc'::TEXT ),
                      ( 103::INT, 'ccc'::TEXT )
    ),
    tryupdate as (
        UPDATE test_upsert SET val = newrow.val
        FROM newrow
        WHERE test_upsert.id = newrow.id
        RETURNING test_upsert.id
    )
    INSERT INTO test_upsert
    SELECT id, val
        FROM newrow
    WHERE id NOT IN ( SELECT id FROM tryupdate );

... and will update or insert each row as called for.

Given that we can do the above, why do we need real UPSERT?  Well, there's some problems with this approximate method:

• It's not concurrency-safe, and can produce unexpected results given really bad timing of multiple connections wanting to update the same rows.
• It will still produce key violation errors given bad concurrency timing, just fewer of them than method 1 above.
• It's still higher overhead than 9.5's UPSERT feature, which is optimized.
• It will return INSERT 0 0  from calls that do only updates, possibly making the app think the upsert failed.
• It's not safe to use with INSERT/UPDATE triggers on the same table.

But ... if you can't wait for 9.5, then at least it's a temporary workaround for you.  In the meantime, download 9.5 alpha and get testing the new UPSERT.

Recursive cycle detection is recursive

In prior posts, I've gone over some methods to prevent cycles from being added to your database.  However, imagine that someone has handed you an existing adjacency list tree -- perhaps migrated from another DBMS -- and you need to find all of the cycles as part of data cleaning?  How do you do that?

One way, obviously, would be just explore all paths and flag the ones where any ID appeared twice:

    WITH RECURSIVE prev AS (
        SELECT folders.id, 1 AS depth, array[id] as seen, false as cycle
        FROM folders
        UNION ALL
        SELECT folders.id, prev.depth + 1, path || folders.id as seen,
            folders.id = any(seen) as cycle
        FROM prev
        INNER JOIN folders on prev.id = parent_id
    )
    SELECT *
    FROM prev;


However, the above has a serious issue: the query itself will cycle and never complete (in fact, it will error out). So we need to terminate each cycle when the first repeat happens.  Fortunately, that's easy to do, and we'll filter for only the cycles while we're at it:

    WITH RECURSIVE prev AS (
        SELECT folders.id, 1 AS depth, array[id] as seen, false as cycle
        FROM folders
        UNION ALL
        SELECT folders.id, prev.depth + 1, seen || folders.id as seen,
            folders.id = any(seen) as cycle
        FROM prev
        INNER JOIN folders on prev.id = parent_id
        AND prev.cycle = false
    )
    SELECT *
    FROM prev
    WHERE cycle = true;

The results of the above query look like this:

    id | depth |       seen       | cycle
   ----+-------+------------------+-------
    21 |     2 | {21,21}          | t
    13 |     5 | {13,14,15,11,13} | t
    14 |     5 | {14,15,11,13,14} | t
    15 |     5 | {15,11,13,14,15} | t
    11 |     5 | {11,13,14,15,11} | t
    (5 rows)

One thing to notice is that you'll get a row for every node in a cycle loop.  That's because with cycles, the choice of starting point is arbitrary.  So the above query isn't the best choice for a deep tree where you have a lot of existing cycles.  There's a 2nd way to detect cycles in a tree; lets see if any of the commentors can post that query.

Fancy SQL Thursday: row-to-column transformation

So, this question came up on IRC today:

"How can I take a one-week date range, and have each date in a separate column?"

This is a good question for an example of row-to-column transformation using Postgres' built-in functions.  While you could use the Tablefunc Extension to do crosstabs, you can also do this on your own in SQL.

First, let's generate a list of formatted dates using Postgres' built-in iterator, generate_series():

    with days as (
        select d, to_char(d, 'Mon DD') as label
        from generate_series($1,$2,interval '1 day') as gs(d)
    )

That generates days in the format "Apr 05" between the dates $1 and $2.  Now comes the tricky part, which is we're going to roll those dates up into an array so that we can transform them horizontally:

    dagg as (
        select array_agg(label order by d) as ld
        from days
    )

So we're using array_agg to make the day label into an array of labels.  We also add the "order by d" to the aggregate to make sure that those days stay in date order.

Once we've got that, then we can just select each array element as a column:

    select ld[1] as d1,
        ld[2] as d2,
        ld[3] as d3,
        ld[4] as d4,
        ld[5] as d5,
        ld[6] as d6,
        ld[7] as d7
    from dagg

Now, this has the limitation that we need to know how many days we're selecting before running the query, but pretty much any method we use requires that if we want columns as output.  So, putting it all together with some sample dates:

    with days as (
        select d, to_char(d, 'Mon DD') as label
        from generate_series('2015-04-01','2015-04-07',interval '1 day') as gs(d)
    ), dagg as (
        select array_agg(label order by d) as ld
        from days
    )
    select ld[1] as d1,
        ld[2] as d2,
        ld[3] as d3,
        ld[4] as d4,
        ld[5] as d5,
        ld[6] as d6,
        ld[7] as d7
    from dagg;

And the result:

      d1   |   d2   |   d3   |   d4   |   d5   |   d6   |   d7  
   --------+--------+--------+--------+--------+--------+--------
    Apr 01 | Apr 02 | Apr 03 | Apr 04 | Apr 05 | Apr 06 | Apr 07

That should help you figure out how to do row-to-column transformations for your own queries.  Enjoy!

Expressions VS advanced aggregates

So ... you're using some of 9.4's new advanced aggregates, including FILTER and WITHIN GROUP.  You want to take some statistical samples of your data, including median, mode, and a count of validated rows.  However, your incoming data is floats and you want to store the samples as INTs, since the source data is actually whole numbers.  Also, COUNT(*) returns BIGINT by default, and you want to round it to INT as well.  So you do this:

    SELECT
        device_id,
        count(*)::INT as present,
        count(*)::INT FILTER (WHERE valid) as valid_count,
        mode()::INT WITHIN GROUP (order by val) as mode,
        percentile_disc(0.5)::INT WITHIN GROUP (order by val)
          as median
    FROM dataflow_0913
    GROUP BY device_id
    ORDER BY device_id;

And you get this unhelpful error message:

    ERROR:  syntax error at or near "FILTER"
    LINE 4:         count(*)::INT FILTER (WHERE valid)
            as valid_count,

And your first thought is that you're not using 9.4, or you got the filter clause wrong.  But that's not the problem.  The problem is that "aggregate() FILTER (where clause)" is a syntactical unit, and cannot be broken up by other expressions.  Hence the syntax error.  The correct expression is this one, with parens around the whole expression and then a cast to INT:

    SELECT
        device_id,
        count(*)::INT as present,
        (count(*) FILTER (WHERE valid))::INT as valid_count,
        (mode() WITHIN GROUP (order by val))::INT as mode,
        (percentile_disc(0.5) WITHIN GROUP (order by val))::INT
           as median
    FROM dataflow_0913
    GROUP BY device_id
    ORDER BY device_id;

If you don't understand this, and you use calculated expressions, you can get a worse result: one which does not produce an error but is nevertheless wrong.  For example, imagine that we were, for some dumb reason, calculating our own average over validated rows.  We might do this:

    SELECT
        device_id,
        sum(val)/count(*) FILTER (WHERE valid) as avg
    FROM dataflow_0913
    GROUP BY device_id
    ORDER BY device_id;

... which would execute successfully, but would give us the total of all rows divided by the count of validated rows. That's because the FILTER clause applies only to the COUNT, and not to the SUM.  If we actually wanted to calculate our own average, we'd have to do this:

    SELECT
        device_id,
        sum(val) FILTER (WHERE valid)
            / count(*) FILTER (WHERE valid) as avg
    FROM dataflow_0913
    GROUP BY device_id
    ORDER BY device_id;

Hopefully that helps everyone who is using the new aggregates to use them correctly and not get mysterious errors.  In the meantime, we can see about making the error messages more helpful.

Fancy SQL Friday: subtracting arrays

Here's one which just came up:  how to see all of the elements in a new array which were not in the old array.  This isn't currently supported by any of PostgreSQL's array operators, but thanks to UNNEST() and custom operators, you can create your own:

    create or replace function diff_elements_text (
        text[], text[] )
    returns text[]
    language sql
    immutable
    as $f$
    SELECT array_agg(DISTINCT new_arr.elem)
    FROM
        unnest($1) as new_arr(elem)
        LEFT OUTER JOIN
        unnest($2) as old_arr(elem)
        ON new_arr.elem = old_arr.elem
    WHERE old_arr.elem IS NULL;
    $f$;

    create operator - (
        procedure = diff_elements_text,
        leftarg = text[],
        rightarg = text[]
    );

Now you can just subtract text arrays:

    josh=# select array['n','z','d','e'] - array['a','n','z'];
    ?column?
    ----------
    {d,e}
    (1 row)

Unfortunately, you'll need to create a new function and operator for each base  type; I haven't been able to get it to work with "anyarray".  But this should save you some time/code on array comparisons.  Enjoy!