Re: Proof of concept: auto updatable views [Review of Patch]

On Wed, Nov 07, 2012 at 05:55:32PM -0500, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Wed, Nov 07, 2012 at 05:04:48PM -0500, Tom Lane wrote:
> >> Should we be doing something
> >> about such cases, or is playing dumb correct?
>
> > The SQL standard handles deciding the behavior based on whether WITH
> > CHECK OPTION is included in the view DDL. See the section 2 of the
> > SQL standard (Foundation) for details.
>
> Ah, I see it. So as long as we don't support WITH CHECK OPTION, we
> can ignore the issue.

I don't think it's as simple as all that. WITH CHECK OPTION is how
the SQL standard allows for creating update-able views in the first
place, so we want to be at least aware of what the standard mandates.

Here's what I'm able to apprehend from the standard.

There are three different WITH CHECK OPTION options:

WITH CHECK OPTION
WITH CASCADED CHECK OPTION
WITH LOCAL CHECK OPTION

- WITH CHECK OPTION means that the results of INSERTs and UPDATEs on
the view must be consistent with the view definition, i.e. INSERTs
any of whose rows would be outside the view or UPDATEs which would
push a row a row out of the view are disallowed.

- WITH CASCADED CHECK OPTION is like the above, but stricter in that
they ensure by checking views which depend on the view where the
write operation is happening. INSERTs and UPDATEs have to "stay in
the lines" for those dependent views.

- WITH LOCAL CHECK OPTION allows INSERTs or UPDATEs that violate the
view definition so long as they comply with the WITH CHECK OPTION on
any dependent views. Apparently the LOCAL here means, "delegate any
CHECK OPTION checking to the dependent view, i.e. check it only
locally and not right here."

Oh, and I'm guessing at least one well-known financial services
company would just love to have these :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate