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

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