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(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

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(at)sonic(dot)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/