Re: WIP patch for updatable security barrier views - Mailing list pgsql-hackers

On 11/21/2013 10:55 PM, Dean Rasheed wrote:
> 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.

Absolutely. This is really a proof of concept to show that we can do DML
directly on a subquery by adding a new RTE for the resultRelation to the
top level of the query.

WITH CHECK OPTION was a casualty of cutting to prove the concept; I know
it needs to be fitted into the subquery based approach. I really haven't
thought about how WITH CHECK OPTION will fit into this, which may be a
mistake - I'm hoping to deal with it after I have the basics working.

There's lots of work to do, some of which will be adapting the code in
your updatable views code to work with this approach, moving them around
where appropriate.

There's also the need to inject columns for UPDATE so the whole tuple is
produced, and deal with DEFAULTs for INSERT.

> 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.

Thanks. At this point I only expect it to work solidly for DELETE, and
was frankly surprised that INSERT worked at all.

The example of the problem is clear and useful, so thanks. I'm still
learning about the handling of attributes and target lists - that's the
next step in work on this.

> 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.

I'm still working a lot of that out. At this point I just wanted to
demonstrate that we can in fact do DML directly on a subquery without
view qual pull-up and view subquery flattening.

One of my main worries is that adding and re-ordering columns needs to
happen from the bottom up - for nested views, it needs to start at the
real base rel and then proceed back up the chain of nested subqueries.
View expansion happens recursively in the rewriter, so this isn't too
easy. I'm thinking of doing it when we hit a real baserel during view
expansion, walking back up the query tree doing fixups then.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services