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.
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.
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.
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!
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.
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.
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.
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.
37 comments
[ 5.1 ms ] story [ 84.4 ms ] threadAuth 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.
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.
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
[0]: http://rhaas.blogspot.co.uk/2015/03/parallel-sequential-scan...
[1]: http://rhaas.blogspot.co.uk/2015/11/parallel-sequential-scan...
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.
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....