If you don't know about RLS I recommend you read up on them[1], as they would allow you to handle select/update/delete rights on rows directly on the data itself.

[1]http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-...

Awesome! Though having read the explanation I can't think of a secure way to use this in a multi-tenanted app, as once the DB user has permission to execute "SET ROLE", it's got the same security issues with accessing other user data as the current "WHERE company_id=3" style of separation? Would it help this use-case, and does it add security?

DB users can only SET ROLE to roles they belong to. If each tenant has its own role it isn't possible to switch between them.

    # CREATE ROLE bob;
    CREATE ROLE
    # CREATE ROLE alice;
    CREATE ROLE
    # SET SESSION AUTHORIZATION bob;
    SET
    > SET ROLE alice;
    ERROR:  permission denied to set role "alice"

The biggest annoyance is with connection pooling and the inability (to my knowledge) to prevent RESET SESSION AUTHORIZATION at the DB-level: when pooling connections you'll want to have a "base" role which is a NOINHERIT member of all "effective" (tenant) roles, so it can only SET ROLE/SET SESSION AUTHORIZATION but can't do anything (so it can't be exploited), the first thing you do when you get a connection from the pool is SSA to the effective user you want, and the last thing you do when you put the connection back in the pool is a RSA.

    # CREATE TABLE foo ( id serial, c1 varchar, c2 varchar );
    # GRANT SELECT (id, c1, c2) ON foo TO public;
    # GRANT UPDATE (c1) ON foo TO bob;
    # GRANT UPDATE (c2) ON foo TO alice;
    # INSERT INTO foo (c1, c2) VALUES ('a', 'b');
    # SELECT * FROM foo;
     id | c1 | c2 
    ----+----+----
      1 | a  | b
    (3 rows)
    # SET SESSION AUTHORIZATION base;
    > UPDATE foo SET c1 = 'd' WHERE id = 1;
    ERROR:  permission denied for relation foo
    > SET SESSION AUTHORIZATION bob;
    > UPDATE foo SET c1 = 'd' WHERE id = 1;
    UPDATE 1
    > SELECT * FROM foo;
     id | c1 | c2 
    ----+----+----
      1 | d  | b
    (3 rows)

It's been discussed on the pg mailing lists but nothing has happened so far

    SELECT '{"k1":"v1"}'::JSONB || '{"k1":"v2","k2":true}'::JSONB

In 9.4 this is the 'best way' I know:

    SELECT
        ('{' || STRING_AGG(
	    '"' || COALESCE(j2.key, j1.key) ||
	    '": ' || TO_JSON(COALESCE(j2.value, j1.value)
        ), ',') || '}')::JSONB
    FROM JSONB_EACH('{"k1":"v1"}') j1
    FULL OUTER JOIN
    (SELECT * FROM JSONB_EACH('{"k1":"v2","k2":true}')) j2 ON j1.key = j2.key

https://github.com/erthalion/jsonbx

I've been thinking that a "test-only" mode of Postgres would be quite useful - i.e no persistence/WAL/crash-safety at all, in return for raw speed. My tests don't need any of this, and the Django test runner is awfully wasteful when it comes to setting up and tearing down tests.

Yes, the new toggle is much more convenient, but you do not have to wait for 9.5 if you really want it.

In our case, we converted some tables we use as kind of materialized views the moment 9.1 came out.

edit: fixed autocorrect wittyness (unclogged => unlogged)

I was also suggesting something further than unlogged tables, maybe something completely in-memory? Good tests are run in isolation and are really just care about the constraints rather than any form of persistence, combined with the fact that all data inserted is erased at the end of the test anyway it just seems a bit wasteful.

> Placing a tablespace on a temporary file system like a ramdisk risks the reliability of the entire cluster

Are you sure putting your test db on a ramdisk won't destroy the development db too?

But there is a way to do it without destroying the db. When you're finished, stop postgres, copy the files off of the ramdisk, and then you can destroy the ramdisk. When you want to start it back up, create the ramdisk, copy the files back, and then start postgres.

Obviously something like MySQL's memory tables would be a better solution.. but postgres doesn't have anything similar. So this is as close as you can get.

So this is really just a convenient syntax.

https://groups.google.com/d/msg/django-developers/IkRgMxTTzP...

I think the performance gained varies quite a bit. I gave it a quick try and saw very minimal gains locally... still need to look into it some more.

You can also turn off fsync in postgresql.conf to stop fsyncs for transactions/checkpoints.

edit: seems like the alpha release is available on the downloads page now: http://www.postgresql.org/download/

    useradd --home-dir /home/postgres \
        --create-home --shell /bin/bash \
        --user-group postgres

    apt-get install -y build-essential libkrb5-dev \
        libxml2 libxml2-dev libxslt1-dev \
        libossp-uuid-dev uuid python-dev \
        libreadline6 libreadline-dev

    ./configure --disable-debug --enable-thread-safety \
        --with-gssapi --with-openssl \
        --with-libxml --with-libxslt \
        --with-ossp-uuid --with-python \
        --without-bonjour

    make world && make install
    sudo su - postgres -c "/usr/local/pgsql/bin/initdb -D /home/postgres/cluster -E UTF8"

I realize we're all professionals here and technical know-how is expected, but nothing about this is "simple". Brings me PTSD of working w/ academics' alphabet soup of "open source" simulation code written in C, Perl and Makefiles.

Build tools have come along away, maybe not with vanilla Postgres installs, but I guess that's what DBAs are paid to know these days.

So let's turn this around, how would you like to see builds become easier?

This can slightly more easily done using 'apt-get build-dep postgresql-9.4'. That'll also pull in the tools to build the docs, but I'd consider that a good thing.

> make world && make install

You're building world, but not installing it -> make install-world.

Could be explained a bit better...