PostgreSQL 9.5 RC1 Released | Hacker News Reader

PostgreSQL 9.5 RC1 Released

postgresql.org

209 pointselchief36 comments

Open articleView on HN

For the juicy stuff, here's the list of what's new in 9.5:

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...

I haven't been following super closely but it looks like parallel sequential scan didn't make it in?

That was a 9.6 feature all along

I think the original ambition was to release it in 9.5 [0], but then it was delayed and rescheduled for 9.6 [1].

[0]: http://rhaas.blogspot.co.uk/2015/03/parallel-sequential-scan...

[1]: http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan...

I wonder if "GROUPING SETS, CUBE and ROLLUP" can be employed to easily do groupwise maximum type queries. I always have to look those up each time I come back to them.

Aren't MAX() and GROUP BY enough for that?

You could do it without, but these shorthand expressions make it very simple to do it quickly and do it right.

But groupwise maximum by some expression is exactly SELECT MAX(expression) GROUP BY (criteria).

You could do details and groupwise maximums together with ROLLUP and some grouping-based conditional expressions in the SELECT clause, or just by UNIONing a detail query with the groupies maximums.

Yes!!

Modification of JSON field values! This makes Postgres a completely functional JSON store.

[1] https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9....

Minor note: That's for JSONB (with a 'B') field values.

I'm excited for row level security, it's going to make auth so much easier to work with when using Postgrest

Can you tell me more about how you're working with Postgrest? It looks really neat, but I haven't heard from anyone using it.

We used it for a small school project where we just needed a database in the cloud but we didn't want to write a backend.

Auth was a pain as you had to write triggers for each table action to check that a user can access a record, but this new row level security will make things easy.

The op was making a joke -- people write "PostgreSQL" or "Postgres" , but never "Postgrest"

OP was talking about this: https://github.com/begriffs/postgrest

UPSERT!

Yes ! Really looking forward to having native upsert instead of custom functions.

Agreed! I've been using this quite heavily with the beta and am really impressed with the syntax (clean and simple) and implementation (atomic!). Certainly better than the equivalent in Oracle, to my eyes at least.

Haven't been keeping up to date with the developments lately. Does anyone know what's the status on master-master replication? That's the one feature I need before I can switch over from MySQL.

It is being worked on. Good summary of TODO: http://www.postgresql.org/message-id/CAMsr+YEfTEp35bQ5ia__nD...

Being worked on as in it won't make it into 9.5? Or being worked on as in they are working out the kinks still

9.5 is frozen. They are just fixing bugs now. 9.6 at the earliest.

I have been using BDR by 2nd Quadrant http://2ndquadrant.com/en-us/resources/bdr/ and so far it is amazing.

Neat. The release notes contain a reference to a statement I wasn't aware of - DROP OWNED BY. Usually I place a DROP TABLE IF EXISTS at the top when trying new things out, but that requires a list of all tables which is somewhat tedious to type. DROP OWNED BY solves that elegantly.

Even simpler:

    DROP SCHEMA public CASCADE;
    CREATE SCHEMA public;

Also, `DROP DATABASE dev_base; CREATE DATABASE dev_base;`

The benefit of recreating the schema is that you can do it without killing your connection.

Very true. I drop all connections, drop the db with all the schemas and force all clients to reconnect. Should eliminate any transient issues.

I was really hoping for statement level instead of triggers. SQL Server has them and they're extremely useful.

I don't think anybody has expressed desire for that feature in a visible way, and I'm quite certain nobody is publicly working on it. While that'll likely not trigger anybody to just work on it, I invite you to send an email explaining what you want. Helps see a couple of those to gauge interest.

I'd love to hear about how you use instead of triggers. I can imagine some useful scenarios for them, but they seem so easily abused ive never really tried them.

Row-Level INSTEAD OF triggers are really useful to make views updateable that are otherwise too complicated to be updateable.

I think I am missing something. Statement level for what?

  create trigger tgr_whatever
  INSTEAD OF {update,insert,delete} on some_complex_view
  [for each row]
  <code goes here>

Sometimes, if you run a DML statement on a view, the database can figure out how to translate that into DML on the underlying tables.

Sometimes it can't. In this case the view is not updateable, unless you define appropriate "instead of" triggers on it. Some databases allow you do define these as statement-level triggers, that is without the "for each row" part. In that case the trigger will be passed all of the affected rows at once.

So far postgres' statement level triggers do not have access to changed rows so far, be it an INSTEAD OF or any other kind of trigger. There has been some noise towards supporting it for AFTER triggers - I doubt we'll see BEFORE support, it's unclear to me what that'd even mean.

I see now. When I read it initially, I read it as "statement level ______ instead of triggers" rather than "statement level INSTEAD OF triggers". I thought a word was missing. Thanks!

Reply on news.ycombinator.com