Re: pg_dumpall blocking other sessions

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