Re: [PATCHES] pg_dump lock timeout

*** a/doc/src/sgml/ref/pg_dump.sgml
--- b/doc/src/sgml/ref/pg_dump.sgml
***************
*** 557,564 **** PostgreSQL documentation
This option disables the use of dollar quoting for function bodies,
and forces them to be quoted using SQL standard string syntax.
</para>
! </listitem>
! </varlistentry>

<varlistentry>
<term><option>--disable-triggers</></term>
--- 557,564 ----
This option disables the use of dollar quoting for function bodies,
and forces them to be quoted using SQL standard string syntax.
</para>
! </listitem>
! </varlistentry>

<varlistentry>
<term><option>--disable-triggers</></term>
***************
*** 588,593 **** PostgreSQL documentation
--- 588,605 ----
</varlistentry>

<varlistentry>
+ <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 may be specified with the same
+ formats as accepted by <command>SET statement_timeout</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--use-set-session-authorization</></term>
<listitem>
<para>
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***************
*** 71,76 **** bool attrNames; /* put attr names into insert strings */
--- 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;
***************
*** 265,270 **** main(int argc, char **argv)
--- 266,275 ----
{"disable-triggers", no_argument, &disable_triggers, 1},
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
+ /*
+ * long options with arguments and no short option letter
+ */
+ {"lock-wait-timeout", required_argument, NULL, 1},

{NULL, 0, NULL, 0}
};
***************
*** 278,283 **** main(int argc, char **argv)
--- 283,289 ----
strcpy(g_opaque_type, "opaque");

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

progname = get_progname(argv[0]);

***************
*** 436,441 **** main(int argc, char **argv)
--- 442,452 ----
/* This covers the long options equivalent to -X xxx. */
break;

+ case 1:
+ /* lock-wait-timeout */
+ lockWaitTimeout = optarg;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
***************
*** 757,762 **** help(const char *progname)
--- 768,776 ----
printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
printf(_(" -v, --verbose verbose mode\n"));
printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
+ printf(_(" --lock-wait-timeout=WAIT_TIME\n"
+ " timeout and fail after waiting WAIT_TIME\n"
+ " for a table lock during startup\n"));
printf(_(" --help show this help, then exit\n"));
printf(_(" --version output version information, then exit\n"));

***************
*** 2956,2962 **** getTables(int *numTables)
int ntups;
int i;
PQExpBuffer query = createPQExpBuffer();
! PQExpBuffer delqry = createPQExpBuffer();
PQExpBuffer lockquery = createPQExpBuffer();
TableInfo *tblinfo;
int i_reltableoid;
--- 2970,2976 ----
int ntups;
int i;
PQExpBuffer query = createPQExpBuffer();
! PQExpBuffer waitquery = createPQExpBuffer();
PQExpBuffer lockquery = createPQExpBuffer();
TableInfo *tblinfo;
int i_reltableoid;
***************
*** 3191,3196 **** getTables(int *numTables)
--- 3205,3218 ----
i_reltablespace = PQfnumber(res, "reltablespace");
i_reloptions = PQfnumber(res, "reloptions");

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

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

/*
***************
*** 3291,3297 **** getTables(int *numTables)
}

destroyPQExpBuffer(query);
! destroyPQExpBuffer(delqry);
destroyPQExpBuffer(lockquery);

return tblinfo;
--- 3317,3323 ----
}

destroyPQExpBuffer(query);
! destroyPQExpBuffer(waitquery);
destroyPQExpBuffer(lockquery);

return tblinfo;
Here is an updated version of this patch against head. It builds, runs and
functions as expected. I did not build the sgml.

I've made changes based on various comments as follows:

- use WAIT_TIME in description consistantly. Reworded for clarity.
(Stephan Frost)

- Use a separate query buffer in getTables() (Stephan Frost)

- sets statement_timeout=0 afterwards per new policy (Tom Lane, Marko Kreen)

- has only --long-option to conserve short option letters (Marko Kreen)

Regards

-dg

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