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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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