Re: Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout - Mailing list pgsql-hackers

On Wed, Apr 16, 2008 at 4:32 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> On Wed, 16 Apr 2008 15:22:31 -0700
>
> "Joshua D. Drake" <jd@commandprompt.com> wrote:
>
>
> > On Wed, 16 Apr 2008 22:17:30 -0000
>  > "Greg Sabino Mullane" <greg@turnstep.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;
staticint      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"));