From: Guillaume Lelarge <guillaume@lelarge.info>
To: Terry Schmitt <tschmitt@schmittworks.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_dumpall blocking other sessions
Date: Thu, 30 Sep 2010 19:11:55 +0200
Message-ID: <4CA4C4DB.50609@lelarge.info> (raw)
In-Reply-To: <AANLkTi=ztzVi4k1KkAMNLSNKjM7SP5tDBmvb1oPbfrMs@mail.gmail.com>
References: <AANLkTi=ztzVi4k1KkAMNLSNKjM7SP5tDBmvb1oPbfrMs@mail.gmail.com>
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.frhttp://dalibo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: guillaume@lelarge.info, tschmitt@schmittworks.com
Subject: Re: pg_dumpall blocking other sessions
In-Reply-To: <4CA4C4DB.50609@lelarge.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox