Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs

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