[Bizgres-general] Another use case

[Jim Nasby]

> From: Chuck McDevitt [mailto:cmcdevitt at greenplum.com]
> Bizgres MPP is different from PostgreSQL or Bizgres in that it takes
> table-level exclusive locks for every DML statement.
> This means any DML statement that gets put on a queue already 
> has locked
> the table it is going to affect before it gets on put on the queue.
> 
> So, no other DML statement can execute on that table, regardless of
> whether the it goes through a queue or not, or wants to be in a
> different queue.

Ugh. No wonder one of our customers said they couldn't use it.
 
> I also worry about how DDL statements will affect this.  They 
> get locks
> on various catalog tables, and if they get their locks before they get
> queued, that seems like a problem (do DDL statements get queued?)

I can't see why they would or should, though it might be wise to code things so that they specifically can't be queued.

> -----Original Message-----
> From: Mark Kirkwood [mailto:mkirkwood at greenplum.com] 
> Sent: Tuesday, August 08, 2006 11:52 PM
> To: Luke Lonergan
> Cc: Jim Nasby; bizgres-general; Chuck McDevitt; John Eshleman; Kurt
> Harriman
> Subject: Re: [Bizgres-general] Another use case
> 
> Luke Lonergan wrote:
> > Mark,
> >    
> >   
> >
> > Please be thick!
> >
> >   
> 
> Always easy to do  :-).
> 
> >> 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?
> >
> >   
> 
> Hmm - there is some investigating to be done with exactly what happens
> with locks - I *do* see some RowExclusive locks popping up in pg_locks
> for queued DML statements - but as they have not been 
> executed yet they
> do not block concurrent exclusive locking (i.e UPDATE/DELETE) to their
> would-be rowsets by other sessions, so it is like the lock entries are
> placeholders created at parse time, without the actual row offsets
> yet...
> 
> > If that's the case, it makes many things simpler.
--
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