Skip site navigation (1) Skip section navigation (2)

pg_dump lock timeout - resend

From: daveg <daveg(at)sonic(dot)net>
To: pgsql-patches(at)postgresql(dot)org
Cc: hari(at)efrontier(dot)com
Subject: pg_dump lock timeout - resend
Date: 2008-05-17 20:32:05
Message-ID: 20080517203205.GJ24768@sonic.net (view raw or flat)
Thread:
Lists: pgsql-patches
I originally sent this a week ago, but there was no response and I do not
see it at:
  http://momjian.postgresql.org/cgi-bin/pgpatches
or
  http://momjian.postgresql.org/cgi-bin/pgpatches_hold
so I assume it got missed in all the excitement about the psql banner.

-----

Subject: [PATCHES] pg_dump lock timeout
Date: Sun, 11 May 2008 04:30:47 -0700

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.


-----

I'll even include the patch in the original mail this time, instead of a
hurried followup.

Thanks again,

-dg

-- 
David Gould       daveg(at)sonic(dot)net      510 536 1443    510 282 0869
If simplicity worked, the world would be overrun with insects.

Responses

pgsql-patches by date

Next:From: Andrew DunstanDate: 2008-05-17 20:45:20
Subject: use of pager on Windows psql
Previous:From: Peter EisentrautDate: 2008-05-17 20:24:05
Subject: pgsql: Don't call rm with empty file list.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group