Re: WITH CHECK OPTION for auto-updatable views

On 5 July 2013 08:22, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
> just some notes:
>
> * autocomplete for INSERT, UPDATE, DELETE should to show updatable views too
>

I think that is the subject for a separate patch. It was discussed
previously and Tom suggested that tab-completion should just complete
with all views regardless of whether they are updatable or not,
because the cost of calling pg_relation_is_updatable() for all views
in a database would be too high, because it would require opening them
all to do the check.

> * can you explain better in doc differences between WITH CASCADED or
> WITH LOCAL OPTION - assign some simple example to doc, please
>

OK, I've added another couple of examples to illustrate the difference.

> * is possible to better identify (describe) failed constraints?
>
> postgres=# create view v1 as select * from bubu where a > 0;
> CREATE VIEW
> postgres=# create view v2 as select * from v1 where a < 10 with check option;
> CREATE VIEW
> postgres=# insert into v1 values(-10);
> INSERT 0 1
> postgres=# insert into v2 values(-10);
> ERROR: new row violates WITH CHECK OPTION for view "v2" --- but this
> constraint is related to v1
> DETAIL: Failing row contains (-10).
>

Hmm, I was originally checking this as a single constraint "a > 0 AND
a < 10" attached to v2, which seemed logical to me, since that's where
the constraint is specified. On closer reading of the SQL spec,
however, it appears that all constraints from inner views are meant to
be checked before the constraints from outer views, which does indeed
then allow for reporting the error corresponding to whichever view's
conditions were violated.

So I've updated the patch to do as you suggest (which makes the code
is little simpler too), and the above test now reports that v1's qual
was violated.

> * I found a difference against MySQL - LOCAL option ignore all other constraints
>
> postgres=# CREATE TABLE t1 (a INT);
> CREATE TABLE
> postgres=# CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 WITH CHECK OPTION;
> CREATE VIEW
> postgres=# CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 WITH LOCAL
> CHECK OPTION;
> CREATE VIEW
> postgres=# INSERT INTO v2 VALUES (2);
> ERROR: new row violates WITH CHECK OPTION for view "v1" -- it will be
> ok on MySQL
> DETAIL: Failing row contains (2).
>
> Probably MySQL is wrong (due differet behave than in DB2) -- but who
> know http://bugs.mysql.com/bug.php?id=6404
>
> What is a correct behave?
>

I think MySQL is wrong here.

The SQL spec is very specific about how these constraints should be
checked (see "Effect of inserting a table into a viewed table"). The
required behaviour is defined recursively such that if a view has a
LOCAL check option, any separately defined checks on lower level views
are checked first, and then any conditions specified locally on the
view are checked. A LOCAL check shouldn't prevent lower level checks
from running.

Thanks for the review. Updated patch attached.

Regards,
Dean