Re: [PATCHES] pg_dump lock timeout

*** pgsql/src/bin/pg_dump/pg_dump.c.orig 2008-05-11 03:23:06.000000000 -0700
--- pgsql/src/bin/pg_dump/pg_dump.c 2008-05-11 03:44:58.000000000 -0700
***************
*** 71,76 ****
--- 71,77 ----
bool schemaOnly;
bool dataOnly;
bool aclsSkip;
+ const char *lockWaitTimeout;

/* subquery used to convert user ID (eg, datdba) to user name */
static const char *username_subquery;
***************
*** 238,243 ****
--- 239,245 ----
{"column-inserts", no_argument, NULL, 'D'},
{"host", required_argument, NULL, 'h'},
{"ignore-version", no_argument, NULL, 'i'},
+ {"lock-wait-timeout", required_argument, NULL, 'l'},
{"no-reconnect", no_argument, NULL, 'R'},
{"oids", no_argument, NULL, 'o'},
{"no-owner", no_argument, NULL, 'O'},
***************
*** 278,283 ****
--- 280,286 ----
strcpy(g_opaque_type, "opaque");

dataOnly = schemaOnly = dumpInserts = attrNames = false;
+ lockWaitTimeout = NULL;

progname = get_progname(argv[0]);

***************
*** 299,305 ****
}
}

! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
--- 302,308 ----
}
}

! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:il:n:N:oOp:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
***************
*** 350,355 ****
--- 353,362 ----
/* ignored, deprecated option */
break;

+ case 'l': /* lock wait time */
+ lockWaitTimeout = optarg;
+ break;
+
case 'n': /* include schema(s) */
simple_string_list_append(&schema_include_patterns, optarg);
include_everything = false;
***************
*** 755,760 ****
--- 762,769 ----
printf(_("\nGeneral options:\n"));
printf(_(" -f, --file=FILENAME output file name\n"));
printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
+ printf(_(" -l, --lock-wait-timeout=DELAY\n"
+ " timeout and fail after delay waiting for a table share lock\n"));
printf(_(" -v, --verbose verbose mode\n"));
printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
printf(_(" --help show this help, then exit\n"));
***************
*** 3191,3196 ****
--- 3200,3213 ----
i_reltablespace = PQfnumber(res, "reltablespace");
i_reloptions = PQfnumber(res, "reloptions");

+ if (lockWaitTimeout)
+ {
+ /* Abandon the dump instead of waiting forever for a table lock */
+ resetPQExpBuffer(lockquery);
+ appendPQExpBuffer(lockquery, "SET statement_timeout = ");
+ appendStringLiteralConn(lockquery, lockWaitTimeout, g_conn);
+ do_sql_command(g_conn, lockquery->data);
+ }
for (i = 0; i < ntups; i++)
{
tblinfo[i].dobj.objType = DO_TABLE;
***************
*** 3259,3264 ****
--- 3276,3285 ----
tblinfo[i].dobj.name);
}

+ if (lockWaitTimeout)
+ {
+ do_sql_command(g_conn, "SET statement_timeout = default");
+ }
PQclear(res);

/*
*** pgsql/doc/src/sgml/ref/pg_dump.sgml.orig 2008-05-11 03:38:05.000000000 -0700
--- pgsql/doc/src/sgml/ref/pg_dump.sgml 2008-05-11 03:38:56.000000000 -0700
***************
*** 302,307 ****
--- 302,320 ----
</varlistentry>

<varlistentry>
+ <term><option>-l <replaceable class="parameter">wait_time</replaceable></option></term>
+ <term><option>--lock-wait-timeout=<replaceable class="parameter">wait_time</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not wait forever for table locks at the start of the dump. Instead
+ time out and abandon the dump if unable to lock a table within the
+ specified wait time. The wait time is specified with the same formats
+ as accepted for intervals by the SET command.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
<term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
<listitem>
On Sun, May 11, 2008 at 04:30:47AM -0700, daveg wrote:
>
> Attached is a patch to add a commandline option to pg_dump to limit how long
> pg_dump will wait for locks during startup.

Ooops, really attached this time.

-dg

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