Hi,

I'm using PG_DUMP for backing up a postgres 7.4 database. As I have
seen, the pg_dump aquires a table lock while dump the table's content.
What will happen, if I have a basic table and several inherited tables.
Will the PG_DUMP tool only aquire locks on the inherited tables or on
the parent-table, too?

Thanks in advance!

Cheers,
Yann

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hi all,

On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote:
>
> I'm using PG_DUMP for backing up a postgres 7.4 database. As I have
> seen, the pg_dump aquires a table lock while dump the table's content.
> What will happen, if I have a basic table and several inherited tables.
> Will the PG_DUMP tool only aquire locks on the inherited tables or on
> the parent-table, too?

Is anyone out there who could answer this question or even give me a
hint about where to read about this if there is any writen things about
that?

Thanks!

Yann

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Yann Michel wrote:
> Hi all,
>
> On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote:
> >
> > I'm using PG_DUMP for backing up a postgres 7.4 database. As I have
> > seen, the pg_dump aquires a table lock while dump the table's content.
> > What will happen, if I have a basic table and several inherited tables.
> > Will the PG_DUMP tool only aquire locks on the inherited tables or on
> > the parent-table, too?
>
> Is anyone out there who could answer this question or even give me a
> hint about where to read about this if there is any writen things about
> that?

You could find out exactly which commands it sends by setting
log_statement=all in a test environment and then dumping things to your
heart's content. Looking at that output you should be able to easily
determine which tables are locked.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

It acquires share locks on EVERY table.

Yann Michel wrote:
> Hi all,
>
> On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote:
>
>>I'm using PG_DUMP for backing up a postgres 7.4 database. As I have
>>seen, the pg_dump aquires a table lock while dump the table's content.
>>What will happen, if I have a basic table and several inherited tables.
>>Will the PG_DUMP tool only aquire locks on the inherited tables or on
>>the parent-table, too?
>
>
> Is anyone out there who could answer this question or even give me a
> hint about where to read about this if there is any writen things about
> that?
>
> Thanks!
>
> Yann
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi,

On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote:
> It acquires share locks on EVERY table.

do you mean on EVERY inherited table once one of them is dumped? Or do
you mean that a share lock is requested(and hold) on each of them once
one is dumped, i.e., sequentially?

Thanks,
Yann

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 2005-11-16, Yann Michel <yann-postgresql@spline.de> wrote:
> Hi,
>
> On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote:
>> It acquires share locks on EVERY table.
>
> do you mean on EVERY inherited table once one of them is dumped? Or do
> you mean that a share lock is requested(and hold) on each of them once
> one is dumped, i.e., sequentially?

pg_dump obtains an ACCESS SHARE lock on _every_ table it dumps, including
the inherited child tables individually, _before_ it starts dumping anything.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

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.

Chris

Yann Michel wrote:
> Hi,
>
> On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote:
>
>>It acquires share locks on EVERY table.
>
>
> do you mean on EVERY inherited table once one of them is dumped? Or do
> you mean that a share lock is requested(and hold) on each of them once
> one is dumped, i.e., sequentially?
>
> Thanks,
> Yann


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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. 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. :-(

Cheers,
Yann

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> 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. :-(

No, all the tables are locked in ACCESS SHARE mode. All that means is
that you cannot alter the schema of the tables in any way.

Chris


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDet+eIB7bNG8LQkwRAhORAJ97IdPGhVXfy+sNryJD4X 839LPuLgCeL/D1
UUPFa1dLVcM+Y6flYxFdfjo=
=AWQK
-----END PGP SIGNATURE-----