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

On Wed, Nov 07, 2012 at 05:04:48PM -0500, Tom Lane wrote:
> Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
> > Thanks for looking at this.
> > Attached is a rebased patch using new OIDs.
>
> I'm starting to look at this patch now. I don't understand the intended
> interaction with qualified INSTEAD rules. The code looks like
>
> + if (!instead && rt_entry_relation->rd_rel->relkind == RELKIND_VIEW)
> + {
> + Query *query = qual_product ? qual_product : parsetree;
> + Query *newquery = rewriteTargetView(query, rt_entry_relation);
>
> which has the effect that if there's a qualified INSTEAD rule, we'll
> apply the substitution transformation to the
> modified-by-addition-of-negated-qual query (ie, qual_product).
> This seems to me to be dangerous and unintuitive, not to mention
> underdocumented. I think it would be better to just not do anything if
> there is any INSTEAD rule, period. (I don't see any problem with DO
> ALSO rules, though, since they don't affect the behavior of the original
> query.)
>
> Also, I didn't see anything in the thread concerning the behavior with
> selective views. If we have say
>
> CREATE VIEW v AS SELECT id, data FROM t WHERE id > 1000;
>
> and we do
>
> INSERT INTO v VALUES(1, 'foo');
>
> the row will be inserted but will then be invisible through the view.
> Is that okay? I can't find anything in the SQL standard that says it
> isn't, but it seems pretty weird. A related example is
>
> UPDATE v SET id = 0 WHERE id = 10000;
>
> which has the effect of making the row disappear from the view, which is
> not what you'd expect an UPDATE to do. 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.

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