[Bizgres-general] Another use case

[Jim Nasby]

Adding the list back in...

> From: Kurt Harriman [mailto:kharriman at greenplum.com]
> Hi Luke,
> 
> Hold on... IMO the resource lock(s) *should* be taken after
> planning so that the plan's cost estimate can be used in
> deciding which queue to put this work item into.
> 
> In MPP, that means we have already acquired the table lock on
> the target table if the statement is INSERT, UPDATE or DELETE.
> 
> This is the right sequence, IMO, given the way locking is
> currently implemented (which should be improved, but that's a
> topic for another day).  Otherwise while an update is waiting
> for its table lock, other queries couldn't run because the
> waiting updater would be holding the resource lock already.

Doesn't this lead to the following situation?

UPDATE big_hairy_table SET field = big_scary_expression;
planner grabs locks
plan costs a lot, gets stuck in a queue
every other DML query on big_hairy_table blocks, even if they were single-row updates
query finally runs 10 minutes later

BINGO! Priority inversion.

I think we'll be needing to release the locks after getting the plan, and then re-aquire them before executing the query.

> I am not yet persuaded by Chuck's objections on this point.
> 
> Regards,
> ... kurt
> 
> Luke Lonergan wrote:
> > Mark,
> >    
> >> Sorry if I'm being thick here
> > 
> > Please be thick!
> > 
> >> So we want to take resource 
> >> locks at planning time?
> > 
> > No, we don't! It's a lot better if this isn't the case.
> > 
> > Our supposition as a group was that that's how it's done - 
> which puzzled
> > me given the topic of prepared statements, etc.
> > 
> >> The current implementation does not 
> >> do this. The resource lock is taken in PortalRun, so a plan 
> >> has been generated and returned
> >> *before* a statement is queued. There is some subtlety with 
> >> cursors, but the previous statement still holds!
> > 
> > OK - does that mean that locks are not taken before a statement is
> > queued?
> > 
> > If that's the case, it makes many things simpler.
> > 
> > - Luke
> > 
> 
>