Skip site navigation (1) Skip section navigation (2)

Re: Reducing some DDL Locks to ShareLock

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reducing some DDL Locks to ShareLock
Date: 2008-10-07 06:21:25
Message-ID: 1223360485.4747.69.camel@ebony.2ndQuadrant (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, 2008-10-06 at 18:57 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > It seems possible to change some DDL commands/subcommands to use a
> > ShareLock rather than an AccessExclusiveLock. Enclosed patch implements
> > this reduction for CREATE TRIGGER, CREATE RULE and ALTER TABLE.
> 
> What happens when two transactions try to do one of these things
> concurrently to the same table?

It should be very similar to CREATE INDEX. If they hold ShareLocks then
their locks do not conflict at relation level.

Updates of their pg_class entry should be handled non-transactionally,
just as they are with CREATE INDEX. This is the reason for the change
from reltriggers being an exact count to being a boolean, i.e. it is
actually now relhastriggers = true | false. Multiple updaters will then
change the value to the same thing.

It was an excellent question because that aspect isn't handled correctly
in the enclosed patch for subcommands, other than index-creating
constraints. 

My main focus is on these commands
* CREATE TRIGGER
* ALTER TABLE ..  ADD PRIMARY KEY
* ALTER TABLE ..  ADD FOREIGN KEY

because those are the most painful ones. We could make it work against
more, but we'd need to rewrite lots and lots of catalog update code.

So I'll make CreateTrigger() use index_update_stats() so we get the
atomic update correct.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2008-10-07 06:44:33
Subject: Re: Transactions and temp tables
Previous:From: Greg SmithDate: 2008-10-07 05:58:16
Subject: Re: Shouldn't pg_settings.enumvals be array of text?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group