On 18th of July, Stephen Frost committed patch:
WITH CHECK OPTION support for auto-updatable VIEWs For simple views which are automatically updatable, this patch allows the user to specify what level of checking should be done on records being inserted or updated. For 'LOCAL CHECK', new tuples are validated against the conditionals of the view they are being inserted into, while for 'CASCADED CHECK' the new tuples are validated against the conditionals for all views involved (from the top down). This option is part of the SQL specification. Dean Rasheed, reviewed by Pavel Stehule
To be honest, at first, I didn't really understand what the patch does. But, after rereading the commit message, and one of my earlier blogposts, the penny dropped.
Let's test it.
I will make a set of table and two views:
$ CREATE TABLE some_data (id int4 PRIMARY KEY, payload text); CREATE TABLE $ INSERT INTO some_data (id, payload) SELECT i, 'payload #' || i FROM generate_series(1, 10) i; INSERT 0 10 $ CREATE VIEW FIRST AS SELECT * FROM some_data WHERE 0 = id % 2; CREATE VIEW $ CREATE VIEW SECOND AS SELECT * FROM FIRST WHERE 0 = id % 3; CREATE VIEW
Data in there is obviously:
$ SELECT * FROM some_data; id | payload ----+------------- 1 | payload #1 2 | payload #2 3 | payload #3 4 | payload #4 5 | payload #5 6 | payload #6 7 | payload #7 8 | payload #8 9 | payload #9 10 | payload #10 (10 ROWS) $ SELECT * FROM FIRST; id | payload ----+------------- 2 | payload #2 4 | payload #4 6 | payload #6 8 | payload #8 10 | payload #10 (5 ROWS) $ SELECT * FROM SECOND; id | payload ----+------------ 6 | payload #6 (1 ROW)
Now, I can insert to the views:
$ INSERT INTO FIRST (id, payload) VALUES (123, 'test1'); INSERT 0 1
The potentially problematic thing is, that I just inserted row that is not visible using the view I used:
$ SELECT * FROM some_data; id | payload -----+------------- 1 | payload #1 2 | payload #2 3 | payload #3 4 | payload #4 5 | payload #5 6 | payload #6 7 | payload #7 8 | payload #8 9 | payload #9 10 | payload #10 123 | test1 (11 ROWS) $ SELECT * FROM FIRST; id | payload ----+------------- 2 | payload #2 4 | payload #4 6 | payload #6 8 | payload #8 10 | payload #10 (5 ROWS)
This is because Pg doesn't check the data in any way. It just lets you insert data, and it will be stored in underlying table.
The patch from Dean, adds a way to make Pg reject rows that wouldn't be visible.
Let's test it. I'll drop both views, and recreate them “WITH CHECK OPTION":
$ DROP VIEW SECOND; DROP VIEW $ DROP VIEW FIRST; DROP VIEW $ CREATE VIEW FIRST AS SELECT * FROM some_data WHERE 0 = id % 2 WITH CHECK OPTION; CREATE VIEW $ CREATE VIEW SECOND AS SELECT * FROM FIRST WHERE 0 = id % 3 WITH CHECK OPTION; CREATE VIEW
and now, while I still can:
$ INSERT INTO FIRST (id, payload) VALUES (14, 'test 14'); INSERT 0 1 $ INSERT INTO SECOND (id, payload) VALUES (18, 'test 18'); INSERT 0 1
I wouldn't be able to insert rows that wouldn't be visible in the views afterwards:
$ INSERT INTO FIRST (id, payload) VALUES (15, '15 is odd, so should not work'); ERROR: NEW ROW violates WITH CHECK OPTION FOR VIEW "first" DETAIL: Failing ROW contains (15, 15 IS odd, so should NOT WORK). $ INSERT INTO SECOND (id, payload) VALUES (21, '21 is divisible by 3, but not by 2'); ERROR: NEW ROW violates WITH CHECK OPTION FOR VIEW "first" DETAIL: Failing ROW contains (21, 15 IS divisible BY 3, but NOT BY 2). $ INSERT INTO SECOND (id, payload) VALUES (16, '16 is even, but not divisible by 3'); ERROR: NEW ROW violates WITH CHECK OPTION FOR VIEW "second" DETAIL: Failing ROW contains (16, 16 IS even, but NOT divisible BY 3).
Nice. This means that it all works. Invalid rows cannot be inserted. The problem is that it is relatively expensive, since inserting to view second, needs to check rules for “second", then find that underlying “table" is not a table, but view – first, and check it's rules too. And then it would have to check the source of rows in first too, but this is a table.
It could be that checking rules in underlying view is redundant.
For example, consider views that “first is – even numbers", and “second is – numbers divisible by 6". If given row matches where clause of second view, then it also matches where of first view, because every number that is divisible by 6 is also divisible by 2.
For such cases, there is “LOCAL CHECK" mode.
So, let's test it:
$ DROP VIEW SECOND; DROP VIEW $ DROP VIEW FIRST; DROP VIEW $ CREATE VIEW FIRST AS SELECT * FROM some_data WHERE 0 = id % 2 WITH LOCAL CHECK OPTION; CREATE VIEW $ CREATE VIEW SECOND AS SELECT * FROM FIRST WHERE 0 = id % 3 WITH LOCAL CHECK OPTION; CREATE VIEW
Let's test-insert:
$ INSERT INTO SECOND (id, payload) VALUES (15, '15 is divisible by 3, but not by 2'); ERROR: NEW ROW violates WITH CHECK OPTION FOR VIEW "first" DETAIL: Failing ROW contains (15, 15 IS divisible BY 3, but NOT BY 2).
Hmm.. why did it fail? Reason lies in the docs as:
New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION).
This means that for sub-view where not to be checked, it can't have check option.
Let's test it:
$ DROP VIEW SECOND; DROP VIEW $ DROP VIEW FIRST; DROP VIEW $ CREATE VIEW FIRST AS SELECT * FROM some_data WHERE 0 = id % 2; CREATE VIEW $ CREATE VIEW SECOND AS SELECT * FROM FIRST WHERE 0 = id % 3 WITH LOCAL CHECK OPTION; CREATE VIEW
And now, test insert:
$ INSERT INTO SECOND (id, payload) VALUES (15, '15 is divisible by 3, but not by 2'); INSERT 0 1
Now it worked.
To be honest – I think that semantics of “LOCAL CHECK" are misleading. And while it is documented, it doesn't sound OK to me. But, if the standard requires it – so be it.
In any way the addition is really cool, as it makes it simple to force certain rules when inserting / updating data.
The most valuable thing about WITH CHECK OPTION is that it lets the DBMS complete the illusion that a VIEW is indistinguishable from a base TABLE in behaviour. The main reason to have VIEWs in the first place is so that each database user/application can be given their own relatively static interface to it, where the actual implementation of the database, such as what are actually base tables and what aren’t, can be changed arbitrarily but the behaviour from the user’s point of view doesn’t have to change. They can use the same tables in the same way regardless of whether they are base tables or virtual tables. Part of the sameness is that if you perform an insert/update/delete on a view foo and subsequently did a select, the result (assuming no other users) would be the same as if foo were a table instead. This includes that successfully INSERTed rows are then visible, which might not happen without WITH CHECK OPTION.