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. -- Sent via pgsql-patches mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches