pgsql: WITH CHECK OPTION support for auto-updatable VIEWs

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

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/4cbe3ac3e86790d05c569de4585e5075a62a9b41

Modified Files
--------------
doc/src/sgml/ref/alter_view.sgml | 5 +
doc/src/sgml/ref/create_view.sgml | 199 ++++++++++----
src/backend/access/common/reloptions.c | 14 +
src/backend/catalog/information_schema.sql | 8 +-
src/backend/catalog/sql_features.txt | 4 +-
src/backend/commands/tablecmds.c | 36 +++
src/backend/commands/view.c | 68 +++++
src/backend/executor/execMain.c | 43 +++
src/backend/executor/nodeModifyTable.c | 33 +++
src/backend/nodes/copyfuncs.c | 18 ++
src/backend/nodes/equalfuncs.c | 15 +
src/backend/nodes/nodeFuncs.c | 14 +
src/backend/nodes/outfuncs.c | 15 +
src/backend/nodes/readfuncs.c | 18 ++
src/backend/optimizer/plan/createplan.c | 15 +-
src/backend/optimizer/plan/planner.c | 30 +-
src/backend/parser/gram.y | 43 ++-
src/backend/rewrite/rewriteHandler.c | 117 +++++++-
src/bin/pg_dump/pg_dump.c | 16 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/include/catalog/catversion.h | 2 +-
src/include/commands/view.h | 2 +
src/include/executor/executor.h | 2 +
src/include/nodes/execnodes.h | 4 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 23 ++
src/include/nodes/plannodes.h | 1 +
src/include/optimizer/planmain.h | 3 +-
src/include/rewrite/rewriteHandler.h | 4 +
src/include/utils/rel.h | 34 +++
src/test/regress/expected/create_view.out | 2 +-
src/test/regress/expected/updatable_views.out | 363 +++++++++++++++++++++++++
src/test/regress/sql/updatable_views.sql | 199 ++++++++++++++
33 files changed, 1245 insertions(+), 107 deletions(-)

On Thu, Jul 18, 2013 at 09:24:02PM +0000, Stephen Frost wrote:
> 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
>
> Branch
> ------
> master

What am I missing here:

create table some_data (id int4 primary key, payload text);
create view first as select * from some_data where 0 = id % 2 with local check option;
create view second as select * from first where 0 = id with local check option;

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).

If I read it correctly, insert to "second" with id = 15 should work,
because the where on "second" matches, and local check shouldn't be
checking on "first"?

Best regards,

depesz

depesz,

moved to -hackers..

* hubert depesz lubaczewski (depesz(at)depesz(dot)com) wrote:
> create table some_data (id int4 primary key, payload text);
> create view first as select * from some_data where 0 = id % 2 with local check option;
> create view second as select * from first where 0 = id with local check option;
>
> 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).

Interesting.

> If I read it correctly, insert to "second" with id = 15 should work,
> because the where on "second" matches, and local check shouldn't be
> checking on "first"?

I think you're half-right, the check should be against 'second', which
should fail because of the '0 = id' check in that view, but we're
reporting the failure as being associated with 'first', which I don't
believe is correct. Dean, thoughts..?

Thanks!

Stephen

On Mon, Jul 29, 2013 at 07:43:53PM -0400, Stephen Frost wrote:
> depesz,
>
> moved to -hackers..
>
> * hubert depesz lubaczewski (depesz(at)depesz(dot)com) wrote:
> > create table some_data (id int4 primary key, payload text);
> > create view first as select * from some_data where 0 = id % 2 with local check option;
> > create view second as select * from first where 0 = id with local check option;
> >
> > 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).
>
> Interesting.
>
> > If I read it correctly, insert to "second" with id = 15 should work,
> > because the where on "second" matches, and local check shouldn't be
> > checking on "first"?
>
> I think you're half-right, the check should be against 'second', which
> should fail because of the '0 = id' check in that view, but we're
> reporting the failure as being associated with 'first', which I don't
> believe is correct. Dean, thoughts..?

the check is "0 = id % 3" - i.e. id has to be multiply of 3. Sorry if my
way of writing conditionals is confusing.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

depesz,

* hubert depesz lubaczewski (depesz(at)depesz(dot)com) wrote:
> On Mon, Jul 29, 2013 at 07:43:53PM -0400, Stephen Frost wrote:
> > * hubert depesz lubaczewski (depesz(at)depesz(dot)com) wrote:
> > > create table some_data (id int4 primary key, payload text);
> > > create view first as select * from some_data where 0 = id % 2 with local check option;
> > > create view second as select * from first where 0 = id with local check option;
[...]
> the check is "0 = id % 3" - i.e. id has to be multiply of 3. Sorry if my
> way of writing conditionals is confusing.

Neither client that I use to read email with saw a '% 3' on the view
definition for 'second' in your original email (or as quoted above).
Still, I do see what you're talking about and will take a look.

Thanks,

Stephen

On 30 July 2013 01:24, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> depesz,
>
> * hubert depesz lubaczewski (depesz(at)depesz(dot)com) wrote:
>> On Mon, Jul 29, 2013 at 07:43:53PM -0400, Stephen Frost wrote:
>> > * hubert depesz lubaczewski (depesz(at)depesz(dot)com) wrote:
>> > > create table some_data (id int4 primary key, payload text);
>> > > create view first as select * from some_data where 0 = id % 2 with local check option;
>> > > create view second as select * from first where 0 = id with local check option;
> [...]
>> the check is "0 = id % 3" - i.e. id has to be multiply of 3. Sorry if my
>> way of writing conditionals is confusing.
>
> Neither client that I use to read email with saw a '% 3' on the view
> definition for 'second' in your original email (or as quoted above).
> Still, I do see what you're talking about and will take a look.
>

Yes it definitely looks like a typo in the test --- the definition of
"first" has "id % 2", so it is checking for even numbers, not for
numbers divisible by 3.

As for the point about which of the checks should be failing, I
believe that the current behaviour is correct. The relevant parts of
SQL:1999 are subclause 14.19 "Effect of inserting a table into a
viewed table", and the related subclause 14.18 "Effect of inserting a
table into a derived table". My interpretation of that is that the
CHECK OPTIONs of base relations should be checked before the CHECK
OPTIONs of outer views, which is how I coded it.

Perhaps it's worth adding a sentence to the docs to make that
explicit. So perhaps immediately before where it says "The CHECK
OPTION may not be used with RECURSIVE views.", a new paragraph saying
something like:

Note that if there is a hierarchy of views on top of other views, and
there are multiple conditions to be checked from different views in the
hierarchy, then any conditions to be checked on underlying base views
will always be checked before any conditions on higher level views.

Regards,
Dean

On Tue, Jul 30, 2013 at 09:23:19AM +0100, Dean Rasheed wrote:
> >> > > create table some_data (id int4 primary key, payload text);
> >> > > create view first as select * from some_data where 0 = id % 2 with local check option;
> >> > > create view second as select * from first where 0 = id with local check option;
> > [...]
> >> the check is "0 = id % 3" - i.e. id has to be multiply of 3. Sorry if my
> >> way of writing conditionals is confusing.
> Yes it definitely looks like a typo in the test --- the definition of
> "first" has "id % 2", so it is checking for even numbers, not for
> numbers divisible by 3.

Sorry, my bad - must have screwed copy/paste.
the second view is:
select * from first where 0 = id % 3 with local check option;

> As for the point about which of the checks should be failing, I
> believe that the current behaviour is correct.

In such case, can you show me what is the difference of "local check"
and "cascaded check"?
Because I assumed, after reading the commit log, that local checks just
the view definition of the view I'm inserting to, and the cascaded
check, checks all the views "upstream".

Given the assumption that current code works correctly - both checks
check also the upstream view.

Best regards,

depesz

On 30 July 2013 11:09, hubert depesz lubaczewski <depesz(at)depesz(dot)com> wrote:
> On Tue, Jul 30, 2013 at 09:23:19AM +0100, Dean Rasheed wrote:
>> >> > > create table some_data (id int4 primary key, payload text);
>> >> > > create view first as select * from some_data where 0 = id % 2 with local check option;
>> >> > > create view second as select * from first where 0 = id with local check option;
>> > [...]
>> >> the check is "0 = id % 3" - i.e. id has to be multiply of 3. Sorry if my
>> >> way of writing conditionals is confusing.
>> Yes it definitely looks like a typo in the test --- the definition of
>> "first" has "id % 2", so it is checking for even numbers, not for
>> numbers divisible by 3.
>
> Sorry, my bad - must have screwed copy/paste.
> the second view is:
> select * from first where 0 = id % 3 with local check option;
>
>> As for the point about which of the checks should be failing, I
>> believe that the current behaviour is correct.
>
> In such case, can you show me what is the difference of "local check"
> and "cascaded check"?
> Because I assumed, after reading the commit log, that local checks just
> the view definition of the view I'm inserting to, and the cascaded
> check, checks all the views "upstream".
>
> Given the assumption that current code works correctly - both checks
> check also the upstream view.
>

Quoting the manual:

LOCAL:
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).

CASCADED:
New rows are checked against the conditions of the view and all
underlying base views. If the CHECK OPTION is specified, and neither
LOCAL nor CASCADED is specified, then CASCADED is assumed.

In particular, note the part about "unless they also specify the CHECK OPTION".

It is defined this way so that if any view has a CHECK OPTION on it,
any views built on top of that can't bypass that check in any way,
they can only add to it.

Taking a specific example, view1 on top of a base table with quals Q1,
and view2 on top of view1 with quals Q2, then inserts into view2 will
check Q1 and/or Q2 depending on the CHECK OPTIONs defined on view1 and
view2 according to the following rules:

view 1 check: NONE LOCAL CASCADED
view2 check:
NONE - Q1 Q1
LOCAL Q2 Q1+Q2 Q1+Q2
CASCADED Q1+Q2 Q1+Q2 Q1+Q2

Regards,
Dean

On Tue, Jul 30, 2013 at 11:45:47AM +0100, Dean Rasheed wrote:
> Quoting the manual:
>
> LOCAL:
> 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).
>
> CASCADED:
> New rows are checked against the conditions of the view and all
> underlying base views. If the CHECK OPTION is specified, and neither
> LOCAL nor CASCADED is specified, then CASCADED is assumed.
>
> In particular, note the part about "unless they also specify the CHECK OPTION".

Ah. All clear now. Sorry for misreading.

Best regards,

depesz