Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 60F602E005E for ; Sun, 11 May 2008 10:00:41 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 50011-05 for ; Sun, 11 May 2008 10:00:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from b.mail.sonic.net (b.mail.sonic.net [64.142.19.5]) by developer.postgresql.org (Postfix) with ESMTP id BBE1B2E0040 for ; Sun, 11 May 2008 10:00:37 -0300 (ADT) Received: from newshell.sonic.net (bolt.sonic.net [208.201.242.19]) by b.mail.sonic.net (8.13.8.Beta0-Sonic/8.13.7) with ESMTP id m4BD0Z8x032229 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sun, 11 May 2008 06:00:35 -0700 Received: from bolt.sonic.net (IDENT:uigA/dZjJSVPmIivf0XNYFTUmw8DHGAG@localhost.localdomain [127.0.0.1]) by newshell.sonic.net (8.13.6/8.12.1) with ESMTP id m4BD0ZRJ014032; Sun, 11 May 2008 06:00:35 -0700 Received: (from daveg@localhost) by bolt.sonic.net (8.13.6/8.12.1/Submit) id m4BD0Zeg014031; Sun, 11 May 2008 06:00:35 -0700 Date: Sun, 11 May 2008 06:00:35 -0700 From: daveg To: pgsql-patches@postgresql.org Cc: hari@efrontier.com Subject: Re: pg_dump lock timeout Message-ID: <20080511130035.GW5673@sonic.net> References: <20080511113047.GV5673@sonic.net> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="QTprm0S8XgL7H0Dt" Content-Disposition: inline In-Reply-To: <20080511113047.GV5673@sonic.net> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/195 X-Sequence-Number: 6221 --QTprm0S8XgL7H0Dt Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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. --QTprm0S8XgL7H0Dt Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="pg_dump.c.timeout_patch" *** 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); /* --QTprm0S8XgL7H0Dt Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="pg_dump.sgml.timeout_patch" *** 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 ---- + + + + + 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. + + + + + --QTprm0S8XgL7H0Dt--