Message-ID: <4CA4C4DB.50609@lelarge.info>
Date: Thu, 30 Sep 2010 19:11:55 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); fr;
	rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
To: Terry Schmitt <tschmitt@schmittworks.com>
CC: pgsql-admin@postgresql.org
Subject: Re: pg_dumpall blocking other sessions
References: <AANLkTi=ztzVi4k1KkAMNLSNKjM7SP5tDBmvb1oPbfrMs@mail.gmail.com>
In-Reply-To: <AANLkTi=ztzVi4k1KkAMNLSNKjM7SP5tDBmvb1oPbfrMs@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Le 30/09/2010 17:50, Terry Schmitt a écrit :
> [...]
> I had a situation where a session was getting blocked while per
> performing a pg_dumpall.
> 
> I could see the pg_dumpall session executing a COPY command on a table
> in one schema.
> Another user session was attempting to truncate a different table in a
> different schema, but in the same database. This session was being
> blocked by the pg_dumpall session.
> 
> I intend to test this further, but my understanding was that pg_dump
> was a non-blocking operation. I'm still on the learning curve with
> Postgres 8.4 (actually EnterpriseDB Advanced Server), so maybe I just
> need more detail on how pg_dumpall works.
> Is the truncate perhaps the problem, but other dml is allowed?
> 

There's a light lock held on every objects so that pg_dump/pg_dumpall
can save every object existing at the time it was fired. This light lock
doesn't allow one to drop or truncate a table because these operations
require exclusive access to the table (which they can't have since
pg_dump/pg_dumpall have a light lock on it). But you can still insert,
update, delete (even all) lines in every table.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com