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

Re: First steps with 8.3 and autovacuum launcher

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: First steps with 8.3 and autovacuum launcher
Date: 2007-10-03 14:02:28
Message-ID: 20071003140228.GC10624@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane escribió:

> >> It might be possible to solve this if we reduce the strength of the lock
> >> used for ALTER TABLE.  We'd have to go through all the commands
> >> potentially issued by a pg_dump script and see if they could all be made
> >> to run concurrently with autovac, which is a bit nervous-making but
> >> might be feasible; and I'm afraid tablecmds.c would need some
> >> restructuring to not use the same lock type for every variant of ALTER.
> >> But it seems like a path worth investigating.

I think this is doable.  We would need to add a phase 0 to ALTER TABLE
processing, which grabs a less strong (than AccessExclusive) lock on the
table, then goes over the list of commands and determine if at least one
of them requires exclusive access to the table (I think the criteria
here is whether table rewriting is needed, in which case AccessExclusive
is enough).  If none of them does, then we press on.

If at least one subcommand needs exclusive lock, then it releases the
first lock and grabs AccessExclusive.  Then it rechecks whether the
table is still OK (not dropped, maybe not renamed)

This is on a new phase 0, and not integrated on phase 1, because this
way we get all the permission checks and, more importantly, inheritance
checks after we have grabbed the correct lock.  (Note: GRANT and REVOKE
do not lock the table itself (only pg_class), so an ALTER TABLE could be
still be running when somebody revokes a needed privilege that was
checked at the start.  Surely this is not a concern for this patch.)

Initially I was proposing RowExclusiveLock for the first lock (because
it's the strongest lock that doesn't conflict with ShareUpdateExclusive
which is what ANALYZE uses).  The problem with this approach is that it
leads to two ALTER TABLE commands being able to run concurrently, which
is a disaster.  To solve that, my idea is to create another lock type,
which conflicts with itself but not with ShareUpdateExclusive.  Not sure
what to call it.

Comments?

-- 
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2007-10-03 16:42:30
Subject: Re: First steps with 8.3 and autovacuum launcher
Previous:From: Magnus HaganderDate: 2007-10-03 13:58:20
Subject: Re: [COMMITTERS] pgsql: Use BIO functions to avoid passing FILE * pointers to OpenSSL

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