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

From Dean Rasheed
Subject Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
Date
Msg-id CAEZATCXL=E64jowSSUiBUJXa4wkmJg3W+JCkM3TgM1gBJ4LN+w@mail.gmail.com
Whole thread Raw
In response to Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs  (Stephen Frost <sfrost@snowman.net>)
Responses Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
List pgsql-hackers
On 30 July 2013 01:24, Stephen Frost <sfrost@snowman.net> wrote:
> depesz,
>
> * hubert depesz lubaczewski (depesz@depesz.com) wrote:
>> On Mon, Jul 29, 2013 at 07:43:53PM -0400, Stephen Frost wrote:
>> > * hubert depesz lubaczewski (depesz@depesz.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
fromdifferent views in the   hierarchy, then any conditions to be checked on underlying base views   will always be
checkedbefore any conditions on higher level views.
 

Regards,
Dean



pgsql-hackers by date:

Previous
From: Cédric Villemain
Date:
Subject: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])
Next
From: hubert depesz lubaczewski
Date:
Subject: Re: [COMMITTERS] pgsql: WITH CHECK OPTION support for auto-updatable VIEWs