December 11th, 2012 by depesz | Tags: , , , , , , , , , , | 4 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

On 8th of December, Tom Lane committed patch:

Support automatically-updatable views.
 
This patch makes "simple" views automatically updatable, without the need
to create either INSTEAD OF triggers or INSTEAD rules.  "Simple" views
are those classified as updatable according to SQL-92 rules.  The rewriter
transforms INSERT/UPDATE/DELETE commands on such views directly into an
equivalent command on the underlying table, which will generally have
noticeably better performance than is possible with either triggers or
user-written rules.  A view that has INSTEAD OF triggers or INSTEAD rules
continues to operate the same as before.
 
For the moment, security_barrier views are not considered simple.
Also, we do not support WITH CHECK OPTION.  These features may be
added in future.
 
Dean Rasheed, reviewed by Amit Kapila

PostgreSQL has writable views for more or less forever. But, they were achievable using not really userfriendly ways. At first, there were rules. These can do the job, but some people (me included) think that rules should be removed from PostgreSQL, buried and forgotten.

Then, in 9.1 we got INSTEAD triggers on views. Which were great, but it's definitely not an automatic way to handle writes to views.

Now, thanks to this patch, we will be able to have automatically modifiable views. How automatically? Let's see:

$ create table test as select i as id, 'depesz #'::text || i as codename from generate_series(1,10) as i;
SELECT 10
 
$ \d test
      Table "public.test"
  Column  |  Type   | Modifiers
----------+---------+-----------
 id       | integer |
 codename | text    |
 
$ create view z as select * from test;
CREATE VIEW
 
$ \d z
        View "public.z"
  Column  |  Type   | Modifiers
----------+---------+-----------
 id       | integer |
 codename | text    |
 
$ update z set codename = 'a' || codename;
UPDATE 10
 
$ select * from z;
 id |  codename
----+-------------
  1 | adepesz #1
  2 | adepesz #2
  3 | adepesz #3
  4 | adepesz #4
  5 | adepesz #5
  6 | adepesz #6
  7 | adepesz #7
  8 | adepesz #8
  9 | adepesz #9
 10 | adepesz #10
(10 rows)

Looks great. But the commit log mentioned “simple". How simple? In documentation we can find list of rules:

  • The view must have exactly one entry in its FROM, which must be a table or another updatable view.
  • The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
  • The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
  • All columns in the view's select list must be simple references to columns of the underlying relation. They cannot be expressions, literals or functions. System columns cannot be referenced, either.
  • No column of the underlying relation can appear more than once in the view's select list.
  • The view must not have the security_barrier property.

Unfortunately, it means that all “cool" views cannot be automatically writable, but that's understandable. Even something as “simple" as:

select * from a join b using (c);

can be ambiguous.

But – as long as your view matches above criteria – it is automatically made writable.

There is also one more cool thing about it. Just as with SELECTs from views – user that uses the view doesn't have to have privileges to underlying table, just has to have privileges to use the view (and view owner has to have privileges to use underlying table).

This means that it is now possible to trivially add rather safe auditing. For example we can store information about who inserted given row, but the user will never see the data:

$ create table data (
    id serial primary key,
    inserted_by text not null default current_user,
    inserted_on timestamptz not null default now(),
    column1 text
);
CREATE TABLE
 
$ create view data_visible as
    select id, column1 from data;
CREATE VIEW

Now, I grant all privileges to data_visible view to test user:

$ grant insert, update, delete, select on data_visible to test;
GRANT
 
$ grant usage on sequence data_id_seq to test;
GRANT

Now, when user test inserts rows, we get following situation:

$ \c - test
You are now connected to database "depesz" as user "test".
 
$ insert into data_visible (column1) values ('test it');
INSERT 0 1
 
$ select * from data_visible;
 id | column1
----+---------
  1 | test it
(1 row)
 
$ select * from data;
ERROR:  permission denied for relation data
 
$ \c - depesz
You are now connected to database "depesz" as user "depesz".
 
$ select * from data;
 id | inserted_by |          inserted_on          | column1
----+-------------+-------------------------------+---------
  1 | test        | 2012-12-11 13:30:11.336758+01 | test it
(1 row)

That is – user test can insert data, and it's automatically “stamped", but the stamp is not visible to user.

And that's about it. Great step forward, and a very important feature. Big thanks to author and reviewer – great job!

  1. 4 comments

  2. # Thomas
    Dec 11, 2012

    That’s good news. Now the only thing missing is the “WITH CHECK OPTION” which prevents inserting of data that could not be retrieved through the view.

  3. Dec 12, 2012

    Finally having automatically updateable views is great, and I’m glad to see it; now the views are closer to what they’re supposed to be, which is to be indistinguishable from tables where at all possible.

    I agree with those that say WITH CHECK OPTION is probably the single most important missing feature now; arguably it is also relatively simple to implement too, compared say to adding support for updating non “simple” views.

  4. # Per Wigren
    Dec 13, 2012

    Great stuff!

    I would argue that materialized views is the single most important missing feature now, in the view domain. Globally, I’d say the most important missing features are MERGE support, multithreaded queries (24+ core servers are common nowadays!) and automatic partitioning.

  5. # farms
    May 17, 2013

    Thanks for these posts, just playing catchup on pg development, ready for 9.3, it’s going to be a great update.

    Was kind of hoping to see CHECK OPTION make it too, looks like someone made a pretty good start:

    http://www.postgresql.org/message-id/CAEZATCWT2Euy_LFGmA4PUiEu41nFZSnEpdLXmcqMrU4UeVmzHg@mail.gmail.com

Leave a comment