On 21 November 2013 13:15, Craig Ringer <craig@2ndquadrant.com> wrote:
> Hi all
>
> I have updatable security barrier views working for INSERT and DELETE,
> so this might be a good chance to see whether the described approach is
> acceptable in reality, not just in theory.
>
> I've been surprised by how well it worked out. I actually landed up
> removing a lot of the existing updatable views code;
I fear you have removed a little too much though.
For example, something somewhere has to deal with re-mapping of
attributes. That's pretty fundamental, otherwise it can't hope to work
properly.
CREATE TABLE t1(a int, b text);
CREATE VIEW v1 AS SELECT b, a FROM t1;
INSERT INTO v1(a, b) VALUES(1, 'B');
ERROR: table row type and query-specified row type do not match
DETAIL: Table has type integer at ordinal position 1, but query expects text.
Also, you have deleted the code that supports WITH CHECK OPTION.
once update knows
> how to operate on a subquery it becomes unnecessary to duplicate the
> optimiser's knowledge of how to expand and flatten a view in the rewriter.
>
> INSERT and DELETE work. I haven't tested INSERT with defaults on the
> base rel yet but suspect it'll need the same support as for update.
>
> UPDATE isn't yet supported because of the need to inject references to
> cols in the base rel that aren't selected in the view.
> expand_targetlist(...) in prep/preptlist.c already does most of this
> work so I hope to be able to use or adapt that.
>
> This patch isn't subject to the replanning and invalidation issues
> discussed for RLS because updatable s.b. views don't depend on the
> current user or some special "bypass RLS" right like RLS would.
>
> The regression tests die because they try to update an updatable view.
>
> This isn't proposed for inclusion as it stands, it's a chance to comment
> and say "why the heck would you do that".
>
It sounds like it could be an interesting approach in principle, but
you haven't yet shown how you intend to replace some of the rewriter
code that you've removed. It feels to me that some of those things
pretty much have to happen in the rewriter, because the planner just
doesn't have the information it needs to be able to do it.
Regards,
Dean
