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

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.

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.

Regards,
Dean