Re: PG_DUMP and table locking in PG7.4

On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote:
> Well, thanks for all the answers. Are the locks then released once they
> are not needed any more like in 2PC?

2PC doesn't release any locks, it can't to maintain integrity.

> That should still leaqve the taken snapshot of the released table in a
> consistent state but might enable other transactions to work on that one
> table once it is released.

ACCESS SHARE means what it says, it stops the table being VACUUMed and
a few other things, but doesn't block INSERTs, UPDATEs or DELETEs.

> I'm asking, because we have a bigger datawarehouse and dump the data for
> a backup every night. Unfortunately, the backup now takes realy long.
> That means, other processes that insert data will have to wait which is
> sometime really long! I was searching for a way to avoid this. I thought
> besides the query-speedub we could also gain some benefit for the backup
> timing... but it sounds, that this will not automatically help me with
> that. :-(

pg_dump doesn't blocks inserts, so your problem must be somewhere
else... Are you running VACUUM anywhere. It's possible that pg_dump is
blocking VACUUM which blocks your inserts...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.