pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dumpall blocking other sessions
2+ messages / 2 participants
[nested] [flat]
* pg_dumpall blocking other sessions
@ 2010-09-30 15:50 Terry Schmitt <tschmitt@schmittworks.com>
0 siblings, 1 reply; 2+ messages in thread
From: Terry Schmitt @ 2010-09-30 15:50 UTC (permalink / raw)
To: pgsql-admin
Hi All,
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?
Thanks much,
Terry
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_dumpall blocking other sessions
@ 2010-09-30 17:11 Guillaume Lelarge <guillaume@lelarge.info>
parent: Terry Schmitt <tschmitt@schmittworks.com>
0 siblings, 0 replies; 2+ messages in thread
From: Guillaume Lelarge @ 2010-09-30 17:11 UTC (permalink / raw)
To: Terry Schmitt <tschmitt@schmittworks.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-09-30 17:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-30 15:50 pg_dumpall blocking other sessions Terry Schmitt <tschmitt@schmittworks.com>
2010-09-30 17:11 ` Guillaume Lelarge <guillaume@lelarge.info>
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