From: daveg <daveg@sonic.net>
To: pgsql-patches@postgresql.org
Cc: hari@efrontier.com
Subject: pg_dump lock timeout
Date: Sun, 11 May 2008 04:30:47 -0700
Message-ID: <20080511113047.GV5673@sonic.net> (raw)
Attached is a patch to add a commandline option to pg_dump to limit how long
pg_dump will wait for locks during startup.
The intent of this patch is to allow pg_dump to fail if a table lock cannot
be taken in a reasonable time. This allows the caller of pg_dump to retry or
otherwise correct the situation, without having locks held for long periods,
and without pg_dump having a long window during which catalog changes can
occur.
It works by setting statement_timeout to the user specified delay during
the startup phase where it is taking access share locks on all the tables.
Once all the locks are taken, it sets statement_timeout back to the default.
If a lock table statement times out, the dump fails with the statement timed
out error.
The orginal motivation was a client who runs heavy batch workloads and uses
truncate table and other DML in long transactions. This has created some
unhappy interaction scenarios with pg_dump:
- pg_dump ends up waiting hours on a DML table lock that is part of a long
transaction. Once the lock is released, pg_dump runs only to find
some table later in the list has been dropped. So pg_dump fails.
- pg_dump waits on a lock while holding access share locks on most of the
tables. Other processes that want to do DML wait on pg_dump. After a
while, large parts of the application are blocked while pg_dump waits
on locks. Eventually the operations staff notice that pg_dump is
blocking production and kill the dump.
Please have a look and consider it for merging.
Thanks
-dg
--
David Gould
If simplicity worked, the world would be overrun with insects.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: daveg@sonic.net, hari@efrontier.com
Subject: Re: pg_dump lock timeout
In-Reply-To: <20080511113047.GV5673@sonic.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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