Re: Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

On Wed, Apr 16, 2008 at 4:32 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On Wed, 16 Apr 2008 15:22:31 -0700
>
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>
>
> > On Wed, 16 Apr 2008 22:17:30 -0000
> > "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
> >
> > > I don't think it's fair to simply discard the use cases provided as
> > > "implausible" and demand one more to your liking. I strongly dislike
> > > having a giant dump file written that has non-vital configuration
> > > variables embedded in the top of it, precluding any user choice
> > > whatsoever[1]. As before, where are the reports of all the people
> > > having their manual restorations interrupted by a statement_timeout?
> >
> > Calling me, wondering why in the world it is happening.
>
> Sorry couldn't help myself. Anyway, in an attempt to be productive, I
> will say that your "where are all the reports" is about as valid as,
> "Where are all the users besides yourself arguing about this having to
> edit a backup file?"
>
> This is a real problem and unless we can find more people to
> substantiate your claim, I think the consensus is to ensure that people
> don't get bit by statement timeout when attempting to do a restore.
>
> I vote in favor of the one less foot gun approach.

Sorry if i missed the obvious reason not to do this... but if its a
command line option the user can choose. Why not something like this
(i did it for pg_dump only...)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ed1b33d..bf9365a 100644
*** a/src/bin/pg_dump/pg_dump.c
--- /bsrc/bin/pg_dump/pg_dump.c
*************** main(int argc, char **argv)
*** 225,230 ****
--- 225,231 ----
int outputNoOwner = 0;
static int use_setsessauth = 0;
static int disable_triggers = 0;
+ static int use_statement_timeout = 0;
char *outputSuperuser = NULL;

RestoreOptions *ropt;
*************** main(int argc, char **argv)
*** 267,272 ****
--- 268,274 ----
{"disable-dollar-quoting", no_argument,
&disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
+ {"use-statement-timeout", no_argument,
&use_statement_timeout, 1},

{NULL, 0, NULL, 0}
};
*************** main(int argc, char **argv)
*** 419,424 ****
--- 421,428 ----
disable_triggers = 1;
else if (strcmp(optarg,
"use-set-session-authorization") == 0)
use_setsessauth = 1;
+ else if (strcmp(optarg,
"use-statement-timeout") == 0)
+ use_statement_timeout = 1;
else
{
fprintf(stderr,
*************** main(int argc, char **argv)
*** 571,576 ****
--- 575,583 ----
*/
do_sql_command(g_conn, "BEGIN");

+ if (!use_statement_timeout)
+ do_sql_command(g_conn, "SET statement_timeout = 0;");
+
do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

/* Select the appropriate subquery to convert user IDs to names */
*************** help(const char *progname)
*** 771,776 ****
--- 778,784 ----
printf(_(" --use-set-session-authorization\n"
" use SESSION
AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set
ownership\n"));
+ printf(_(" --use-statement-timeout respect statement_timeout\n"));

printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or
socket directory\n"));