The following patches add a -N option to psql and pgrestore.

This option adds a BEGIN at the start and a COMMIT at the end of all
commands, causing all statements to be executed as a single transaction.

In pgrestore the -N option also forces the -e option: exit on error.

Passes make check on cvstip, plus some other basic testing.
No docs as yet.

Patches are completely independent of each other, the only connection
between them is at the conceptual level.

[Why have I done this? This is a precursor to the introduction of
WAL-bypass for COPY, when run in the same transaction in which a table
was created. Together, they will allow faster upgrades and restores.]

Best Regards, Simon Riggs
Index: src/bin/psql/command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.155
diff -c -r1.155 command.c
*** src/bin/psql/command.c	8 Dec 2005 21:18:22 -0000	1.155
--- src/bin/psql/command.c	16 Dec 2005 18:02:12 -0000
***************
*** 523,529 ****
  		else
  		{
  			expand_tilde(&fname);
! 			success = (process_file(fname) == EXIT_SUCCESS);
  			free(fname);
  		}
  	}
--- 523,529 ----
  		else
  		{
  			expand_tilde(&fname);
! 			success = (process_file(fname, false) == EXIT_SUCCESS);
  			free(fname);
  		}
  	}
***************
*** 1317,1327 ****
   * MainLoop() error code.
   */
  int
! process_file(char *filename)
  {
  	FILE	   *fd;
  	int			result;
  	char	   *oldfilename;
  
  	if (!filename)
  		return EXIT_FAILURE;
--- 1317,1328 ----
   * MainLoop() error code.
   */
  int
! process_file(char *filename, bool single_txn)
  {
  	FILE	   *fd;
  	int			result;
  	char	   *oldfilename;
+ 	PGresult   *res; 
  
  	if (!filename)
  		return EXIT_FAILURE;
***************
*** 1337,1343 ****
--- 1338,1350 ----
  
  	oldfilename = pset.inputfile;
  	pset.inputfile = filename;
+ 
+     if (single_txn)
+         res = PSQLexec("BEGIN", false);
  	result = MainLoop(fd);
+     if (single_txn)
+         res = PSQLexec("COMMIT", false);
+ 
  	fclose(fd);
  	pset.inputfile = oldfilename;
  	return result;
Index: src/bin/psql/command.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.h,v
retrieving revision 1.22
diff -c -r1.22 command.h
*** src/bin/psql/command.h	1 Jan 2005 05:43:08 -0000	1.22
--- src/bin/psql/command.h	16 Dec 2005 18:02:12 -0000
***************
*** 28,34 ****
  extern backslashResult HandleSlashCmds(PsqlScanState scan_state,
  				PQExpBuffer query_buf);
  
! extern int	process_file(char *filename);
  
  extern bool do_pset(const char *param,
  		const char *value,
--- 28,34 ----
  extern backslashResult HandleSlashCmds(PsqlScanState scan_state,
  				PQExpBuffer query_buf);
  
! extern int	process_file(char *filename, bool single_txn);
  
  extern bool do_pset(const char *param,
  		const char *value,
Index: src/bin/psql/help.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.106
diff -c -r1.106 help.c
*** src/bin/psql/help.c	15 Oct 2005 02:49:40 -0000	1.106
--- src/bin/psql/help.c	16 Dec 2005 18:02:13 -0000
***************
*** 92,97 ****
--- 92,98 ----
  	printf(_("  -d DBNAME       specify database name to connect to (default: \"%s\")\n"), env);
  	puts(_("  -c COMMAND      run only single command (SQL or internal) and exit"));
  	puts(_("  -f FILENAME     execute commands from file, then exit"));
+ 	puts(_("  -N              execute command file as a single transaction\n"));
  	puts(_("  -l              list available databases, then exit"));
  	puts(_("  -v NAME=VALUE   set psql variable NAME to VALUE"));
  	puts(_("  -X              do not read startup file (~/.psqlrc)"));
Index: src/bin/psql/startup.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/startup.c,v
retrieving revision 1.128
diff -c -r1.128 startup.c
*** src/bin/psql/startup.c	22 Nov 2005 18:17:29 -0000	1.128
--- src/bin/psql/startup.c	16 Dec 2005 18:02:13 -0000
***************
*** 76,81 ****
--- 76,82 ----
  	char	   *action_string;
  	bool		no_readline;
  	bool		no_psqlrc;
+     bool        single_txn;
  };
  
  static int	parse_version(const char *versionString);
***************
*** 268,274 ****
  		if (!options.no_psqlrc)
  			process_psqlrc(argv[0]);
  
! 		successResult = process_file(options.action_string);
  	}
  
  	/*
--- 269,275 ----
  		if (!options.no_psqlrc)
  			process_psqlrc(argv[0]);
  
! 		successResult = process_file(options.action_string, options.single_txn);
  	}
  
  	/*
***************
*** 425,430 ****
--- 426,432 ----
  		{"list", no_argument, NULL, 'l'},
  		{"log-file", required_argument, NULL, 'L'},
  		{"no-readline", no_argument, NULL, 'n'},
+ 		{"single-txn", no_argument, NULL, 'N'},
  		{"output", required_argument, NULL, 'o'},
  		{"port", required_argument, NULL, 'p'},
  		{"pset", required_argument, NULL, 'P'},
***************
*** 453,459 ****
  
  	memset(options, 0, sizeof *options);
  
! 	while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:HlL:no:p:P:qR:sStT:uU:v:VWxX?",
  							long_options, &optindex)) != -1)
  	{
  		switch (c)
--- 455,461 ----
  
  	memset(options, 0, sizeof *options);
  
! 	while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:HlL:nNo:p:P:qR:sStT:uU:v:VWxX?",
  							long_options, &optindex)) != -1)
  	{
  		switch (c)
***************
*** 505,510 ****
--- 507,515 ----
  			case 'n':
  				options->no_readline = true;
  				break;
+ 			case 'N':
+ 				options->single_txn = true;
+ 				break;
  			case 'o':
  				setQFout(optarg);
  				break;
***************
*** 690,698 ****
  	sprintf(psqlrc, "%s-%s", filename, PG_VERSION);
  
  	if (access(psqlrc, R_OK) == 0)
! 		(void) process_file(psqlrc);
  	else if (access(filename, R_OK) == 0)
! 		(void) process_file(filename);
  	free(psqlrc);
  }
  
--- 695,703 ----
  	sprintf(psqlrc, "%s-%s", filename, PG_VERSION);
  
  	if (access(psqlrc, R_OK) == 0)
! 		(void) process_file(psqlrc, false);
  	else if (access(filename, R_OK) == 0)
! 		(void) process_file(filename, false);
  	free(psqlrc);
  }
  
Index: src/bin/pg_dump/pg_backup.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup.h,v
retrieving revision 1.37
diff -c -r1.37 pg_backup.h
*** src/bin/pg_dump/pg_backup.h	15 Oct 2005 02:49:38 -0000	1.37
--- src/bin/pg_dump/pg_backup.h	16 Dec 2005 17:57:29 -0000
***************
*** 115,120 ****
--- 115,122 ----
  
  	int			suppressDumpWarnings;	/* Suppress output of WARNING entries
  										 * to stderr */
+     bool        single_txn;
+ 
  } RestoreOptions;
  
  /*
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.118
diff -c -r1.118 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c	22 Nov 2005 18:17:28 -0000	1.118
--- src/bin/pg_dump/pg_backup_archiver.c	16 Dec 2005 17:57:31 -0000
***************
*** 217,222 ****
--- 217,225 ----
  
  	AH->stage = STAGE_PROCESSING;
  
+     if (ropt->single_txn)
+ 		ahprintf(AH, "BEGIN;\n\n");
+ 
  	/*
  	 * Drop the items at the start, in reverse order
  	 */
***************
*** 365,370 ****
--- 368,376 ----
  		}
  	}
  
+     if (ropt->single_txn)
+ 		ahprintf(AH, "COMMIT;\n\n");
+ 
  	if (AH->public.verbose)
  		dumpTimestamp(AH, "Completed on", time(NULL));
  
Index: src/bin/pg_dump/pg_restore.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_restore.c,v
retrieving revision 1.73
diff -c -r1.73 pg_restore.c
*** src/bin/pg_dump/pg_restore.c	15 Oct 2005 02:49:39 -0000	1.73
--- src/bin/pg_dump/pg_restore.c	16 Dec 2005 17:57:31 -0000
***************
*** 105,110 ****
--- 105,111 ----
  		{"password", 0, NULL, 'W'},
  		{"schema", 1, NULL, 'n'},
  		{"schema-only", 0, NULL, 's'},
+ 		{"single-txn", 0, NULL, 'N'},
  		{"superuser", 1, NULL, 'S'},
  		{"table", 1, NULL, 't'},
  		{"trigger", 1, NULL, 'T'},
***************
*** 142,148 ****
  		}
  	}
  
! 	while ((c = getopt_long(argc, argv, "acCd:ef:F:h:iI:lL:n:Op:P:RsS:t:T:uU:vWxX:",
  							cmdopts, NULL)) != -1)
  	{
  		switch (c)
--- 143,149 ----
  		}
  	}
  
! 	while ((c = getopt_long(argc, argv, "acCd:ef:F:h:iI:lL:n:NOp:P:RsS:t:T:uU:vWxX:",
  							cmdopts, NULL)) != -1)
  	{
  		switch (c)
***************
*** 185,193 ****
--- 186,205 ----
  				opts->tocFile = strdup(optarg);
  				break;
  
+ 			case 'n':			/* Dump data for this schema only */
+ 				opts->selTypes = 1;
+ 				opts->schemaNames = strdup(optarg);
+ 				break;
+ 
+ 			case 'N':			/* Restore data in a single transaction */
+ 				opts->single_txn = true;
+ 				opts->exit_on_error = true;
+ 				break;
+ 
  			case 'O':
  				opts->noOwner = 1;
  				break;
+ 
  			case 'p':
  				if (strlen(optarg) != 0)
  					opts->pgport = strdup(optarg);
***************
*** 223,233 ****
  				opts->tableNames = strdup(optarg);
  				break;
  
- 			case 'n':			/* Dump data for this schema only */
- 				opts->selTypes = 1;
- 				opts->schemaNames = strdup(optarg);
- 				break;
- 
  			case 'u':
  				opts->requirePassword = true;
  				opts->username = simple_prompt("User name: ", 100, true);
--- 235,240 ----
***************
*** 380,385 ****
--- 387,393 ----
  	printf(_("  -L, --use-list=FILENAME  use specified table of contents for ordering\n"
  			 "                           output from this file\n"));
  	printf(_("  -n, --schema=NAME        restore only objects in this schema\n"));
+ 	printf(_("  -N, --single-txn         restore as a single transaction\n"));
  	printf(_("  -O, --no-owner           skip restoration of object ownership\n"));
  	printf(_("  -P, --function=NAME(args)\n"
  			 "                           restore named function\n"));
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to