Subject: Re: cost-based vacuum
From: Ian Westmacott <ianw@intellivid.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1121081504.3970.53.camel@localhost.localdomain>
References: <1120839902.20657.197.camel@spectre.intellivid.com>
	<1121081504.3970.53.camel@localhost.localdomain>
Content-Type: text/plain
Organization: Intellivid Corp.
Message-Id: <1121087266.27427.35.camel@spectre.intellivid.com>
Mime-Version: 1.0
Date: Mon, 11 Jul 2005 09:07:46 -0400
Content-Transfer-Encoding: 7bit

On Mon, 2005-07-11 at 07:31, Simon Riggs wrote:
> The ANALYZE commands hold read locks on the tables you wish to write to.
> If you slow them down, you merely slow down your write transactions
> also, and then the read transactions that wait behind them. Every time
> the ANALYZE sleeps it wakes up the other transactions, which then
> realise they can't move because of locks and then wake up the ANALYZEs
> for another shot. The end result is that you introduce more context-
> switching, without any chance of doing more useful work while the
> ANALYZEs sleep.

Let me make sure I understand.  ANALYZE acquires a read
lock on the table, that it holds until the operation is
complete (including any sleeps).  That read lock blocks
the extension of that table via COPY.  Is that right?

According to the 8.0 docs, ANALYZE acquires an ACCESS SHARE
lock on the table, and that conflicts only with ACCESS
EXCLUSIVE.  Thats why I didn't think I had a lock issue,
since I think COPY only needs ROW EXCLUSIVE.  Or perhaps
the transaction needs something more?

Thanks,

	--Ian