Thread: pg_dump lock timeout - resend
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@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.
Attachment
daveg wrote: > I originally sent this a week ago, but there was no response and I do not > see it > Nope. FYI, the right link is [1] and your patch [2] is in the queue for July Commit Fest. [1] http://wiki.postgresql.org/wiki/Development_information [2] http://wiki.postgresql.org/wiki/CommitFest:July -- Euler Taveira de Oliveira http://www.timbira.com/
On Sat, May 17, 2008 at 06:55:27PM -0300, Euler Taveira de Oliveira wrote: > daveg wrote: > > >I originally sent this a week ago, but there was no response and I do not > >see it > > > Nope. FYI, the right link is [1] and your patch [2] is in the queue for > July Commit Fest. > > [1] http://wiki.postgresql.org/wiki/Development_information > [2] http://wiki.postgresql.org/wiki/CommitFest:July Thanks for the pointers. I tried finding this from the main postgresql.org developer section, so perhaps I am obtuse, or perhaps the commitfest info is not that easy to find. -dg -- David Gould daveg@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.
daveg wrote: > On Sat, May 17, 2008 at 06:55:27PM -0300, Euler Taveira de Oliveira wrote: >> daveg wrote: >> >>> I originally sent this a week ago, but there was no response and I do not >>> see it >>> >> Nope. FYI, the right link is [1] and your patch [2] is in the queue for >> July Commit Fest. >> >> [1] http://wiki.postgresql.org/wiki/Development_information >> [2] http://wiki.postgresql.org/wiki/CommitFest:July > > Thanks for the pointers. I tried finding this from the main postgresql.org > developer section, so perhaps I am obtuse, or perhaps the commitfest info is > not that easy to find. The pages could certainly stand an updating to reflect how development currently commences. I will work up a patch next week. Joshua D. Drake > > -dg >
Joshua D. Drake wrote: > The pages could certainly stand an updating to reflect how development > currently commences. I will work up a patch next week. > IMHO, this development information needs to be at [1]. [1] http://www.postgresql.org/developer/roadmap -- Euler Taveira de Oliveira http://www.timbira.com/