Re: PG_DUMP and table locking in PG7.4

Yann Michel wrote:
> Hi,
> 
> On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote:
> 
>>I belive a lock is acquired on every table including inherited children 
>>BEFORE doing ANY dumping.  To allow pg_dump to get a consistent dump 
>>snapshot.
> 
> Well, thanks for all the answers. Are the locks then released once they
> are not needed any more like in 2PC?
> 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. 
> 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.

No, a share lock on the table does not mean that other transactions 
can't insert or update anymore. What it does, is to prevent tables from 
being dropped or truncated.
To get a consistent snapshot of the data in the tables itself, pg_dump 
just uses the SERIALIZABLE transaction isolation level. This is 
implemented via MVCC in postgresql, which makes sure that neither 
readers nor writers will block waiting.

So only if you do full table locks in your application (using LOCK TABLE 
statements), you will suffer from pg_dump backups.

Best Regards,
Michael Paesold