[Bizgres-general] Another use case

[Jim C. Nasby]

On Thu, Aug 10, 2006 at 12:00:24PM +1200, Mark Kirkwood wrote:
> Jim Nasby wrote:
> >
> > 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.
> >
> >   
> No, not for Postgres or Bizgres anyway - see my previous mail about 
> RowExclusive locks.  Other sessions can happily carry on updating 
> 'big_hairy_table' :-).  I think what is happening is that a RowExclusive 
> lock is created at parse time (or thereabouts), but no tuple locks are 
> taken till the executor is run to identify said tuples.

DOH! I should have said DDL in there, not DML. Not quite as big a deal,
but still something that needs to be considered. Maybe there needs to be
a way to force statements that are sitting in-queue to drop their table
locks and then re-aquire them (and deal with the consequences).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461