[Bizgres-general] Another use case

[Gavin Sherry]

On Fri, 11 Aug 2006, Mark Kirkwood wrote:

> Jim C. Nasby wrote:
> > On Thu, Aug 10, 2006 at 12:00:24PM +1200, Mark Kirkwood wrote:
> >
> >>
> >> 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).
> >
>
>
> Currently I'm only considering SELECT, INSERT, UPDATE, DELETE, DECLARE
> for locking, as I didn't want to mess with DDL.

Right. There are only a few interesting long running DDLs for which
queueing makes sense (ALTER TABLE ADD COLUMN, ALTER TYPE, VACUUM, etc).
What you do need to consider is that DDL which is not queued will not run
because the enqueued DML will have AccessShareLock on tables.

> However, I'm looking at your suggestion, as it may be generally good to
> be able to unlock all target relations in the query(s) before waiting on
> a resource lock. It is pretty easy to find out what the target relations
> are for a query (examine range table entry for the query's listed target
> relation index), but determining what sort of lock is on the relation is
> not so obvious (could scan the lock hash, but may find several locks of
> different modes), I may have to punt for RowExclusive unless we are MPP!

This is where it gets fairly ... difficult. Say you this:

1) Backend A: SELECT a, sum(b) FROM bigtable ...

2) Backend A: queue statement as <stmt>

3) Backend B: ALTER TABLE bigtable DROP COLUMN b;

4) Backend B: force <stmt> to drop its locks

5) Backend B: complete DROP COLUMN

...

6) Run <stmt>

There's a good reason DDL take out locks which conflict with DML
statements: they will change the table under the DML and the DML will fail
in spectacular fashion. I'm not sure how you should handle this.

With MPP it's different, because it takes out an exclusive lock for
everything. Maybe it needs a new lock of its own so that conflicting DMLs
can recognise each other?

Thanks,

Gavin