Re: Odd behavior of updatable security barrier views on foreign tables

Dean,

* Dean Rasheed (dean(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
> On 9 February 2015 at 21:17, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >> > On Fri, Jan 30, 2015 at 5:20 AM, Etsuro Fujita
> >> > > I noticed that when updating security barrier views on foreign tables,
> >> > > we fail to give FOR UPDATE to selection queries issued at ForeignScan.
> >>
> > I've looked into this a fair bit more over the weekend and the issue
> > appears to be that the FDW isn't expecting a do-instead sub-query.
> > I've been considering how we might be able to address that but havn't
> > come up with any particularly great ideas and would welcome any
> > suggestions. Simply having the FDW try to go up through the query would
> > likely end up with too many queries showing up with 'for update'. We
> > add the 'for update' to the sub-query before we even get called from
> > the 'Modify' path too, which means we can't use that to realize when
> > we're getting ready to modify rows and therefore need to lock them.
> >
> > In any case, I'll continue to look but would welcome any other thoughts.
>
> Sorry, I didn't have time to look at this properly. My initial thought
> is that expand_security_qual() needs to request a lock on rows coming
> from the relation it pushes down into a subquery if that relation was
> the result relation, because otherwise it won't have any locks, since
> preprocess_rowmarks() only adds PlanRowMarks to non-target relations.

Yes, that works. I had been focused on trying to figure out a way to
make this work just in the FDW, but you're right, fixing it in
expand_security_qual() looks like the right approach.

> Of course that means that it may end up locking more rows than are
> actually updated, but that's essentially the same as a SELECT FOR
> UPDATE on a s.b. view right now.

Agreed.

Thanks!

Stephen