[PATCHES] Single-Transaction Utility options
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 - 1.155
--- src/bin/psql/command.c 16 Dec 2005 18:02:12 -
***
*** 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 - 1.22
--- src/bin/psql/command.h 16 Dec 2005 18:02:12 -
***
*** 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 - 1.106
--- src/bin/psql/help.c 16 Dec 2005 18:02:13 -
***
*** 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 - 1.128
--- src/bin/psql/startup.c 16 Dec 2005 18:02:13 -
***
*** 76,81
--- 76,82
char *action_string;
bool no_readline;
bool no_psqlrc;
+ boolsingle_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,
Re: [PATCHES] Single-Transaction Utility options
Simon Riggs [EMAIL PROTECTED] writes: The following patches add a -N option to psql and pgrestore. -N seems an entirely random name for the switch ... can't we do better? I see that -t, -T, -s, -S, -x and -X are all taken, which lets out the obvious choices ... but I'd rather have no single-letter abbreviation at all than one that has zero relationship to the function of the switch. Would -1 work, or just confuse people? Also, I don't actually see any point to this in psql, as you can always do begin; \i file end; It's only pg_restore that you really need it for. Dropping the psql part of the patch might give us a little more maneuvering room as far as the switch name goes. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Single-Transaction Utility options
Simon Riggs wrote: 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. Why use it around the whole file and not only around that particular table's operations? Also why force it to activate the abort-on-error mode? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Win32 gettimeofday() comment patch
Add a note to Win32 gettimeofday() emulation. Regards, Qingqing --- Index: gettimeofday.c === RCS file: /projects/cvsroot/pgsql/src/port/gettimeofday.c,v retrieving revision 1.6 diff -c -r1.6 gettimeofday.c *** gettimeofday.c 16 May 2005 05:52:13 - 1.6 --- gettimeofday.c 16 Dec 2005 19:37:41 - *** *** 36,41 --- 36,43 /* * timezone information is stored outside the kernel so tzp isn't used anymore. + * Note: this function is not for Win32 high precision timing purpose, see + * elapsed_time() etc. */ int gettimeofday(struct timeval * tp, struct timezone * tzp) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Single-Transaction Utility options
On Fri, 2005-12-16 at 13:59 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The following patches add a -N option to psql and pgrestore. -N seems an entirely random name for the switch ... can't we do better? I see that -t, -T, -s, -S, -x and -X are all taken, which lets out the obvious choices ... but I'd rather have no single-letter abbreviation at all than one that has zero relationship to the function of the switch. Almost. Stands for traNsaction Would -1 work, or just confuse people? That was my preference, I just thought it wouldn't be popular... So I'll happily change that. Also, I don't actually see any point to this in psql, as you can always do begin; \i file end; It's only pg_restore that you really need it for. Dropping the psql part of the patch might give us a little more maneuvering room as far as the switch name goes. Of course, you're right... and that is all the patch does in fact. It seemed easier to do -1 than the SQL above, especially when doing it from a single command line. But the main reason was to make all the utilities work the same, if possible. Having different options on each utility makes it easier to make mistakes, so I'd rather have the same option everywhere that it could apply. I couldn't see any reason not to do it, either. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Single-Transaction Utility options
On Fri, 2005-12-16 at 16:04 -0300, Alvaro Herrera wrote: Simon Riggs wrote: 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. Why use it around the whole file and not only around that particular table's operations? You could. That just behaves slightly differently. Maybe we should have options for both? Also why force it to activate the abort-on-error mode? For what reason would you want it to keep running? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Single-Transaction Utility options
Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2005-12-16 at 13:59 -0500, Tom Lane wrote: Would -1 work, or just confuse people? That was my preference, I just thought it wouldn't be popular... So I'll happily change that. OK. While you're at it, I didn't like the long name either ;-). We do not use the abbrevation txn anywhere, so I think it's bad to introduce it here. I'd vote for spelling out --single-transaction, or maybe just --single. I believe you can abbreviate long switch names to any unique prefix, so there's not really any more typing here. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO Item - Add system view to show free space map
Mark Kirkwood wrote: Bruce Momjian wrote: Mark Kirkwood wrote: Simon Riggs wrote: I like this, but not because I want to read it myself, but because I want to make autovacuum responsible for re-allocating free space when it runs out. This way we can have an autoFSM feature in 8.2 Not wanting to denigrate value of the interesting but slightly OT direction this thread has taken - but does anybody want to comment/review the patch itself :-) ? I saw this question about a transaction block and your reply: http://archives.postgresql.org/pgsql-patches/2005-10/msg00226.php but no new patch. I know someone suggested pgfoundry but it seems most natural in /contrib. Do you want to update the patch? In the expectation of further revisions, I was going to batch that one in with the 'rest' - given that there have not been any, I'll submit a revised patch. Here it is - I seem to have had trouble sending any attachments to this list recently. Bruce the patch (sent privately), so its in the patches queue, but thought I would have another go at getting it to -patches so others can review it more easily! cheers Mark contrib-freespacemap.patch.gz Description: application/gzip ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Win32 gettimeofday() comment patch
Qingqing Zhou wrote: Add a note to Win32 gettimeofday() emulation. Applied, thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Single-Transaction Utility options
Simon Riggs wrote: On Fri, 2005-12-16 at 16:04 -0300, Alvaro Herrera wrote: Simon Riggs wrote: 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. Why use it around the whole file and not only around that particular table's operations? You could. That just behaves slightly differently. Maybe we should have options for both? Are they different enough to warrant having two switches? IIRC the point was to have the COPY in the same transaction as the CREATE TABLE, right? In what way is it worse to have each table in its own transaction? Also why force it to activate the abort-on-error mode? For what reason would you want it to keep running? To restore the rest of the tables in the dump I presume ... I mean, the behaviors are orthogonal really (_if_ you take the stance that this should be used on a per table basis rather than a file basis, that is. Because if you abort the transaction then clearly there's no point in keep running, as everything will be rejected by the server anyway.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Single-Transaction Utility options
Simon Riggs [EMAIL PROTECTED] writes: Why use it around the whole file and not only around that particular table's operations? You could. That just behaves slightly differently. pg_dump does not always produce all the commands affecting a single table together, so I don't think you can actually get the desired results --- certainly it would be a nontrivial amount of work to get any useful behavior like that. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Single-Transaction Utility options
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Why use it around the whole file and not only around that particular table's operations? You could. That just behaves slightly differently. pg_dump does not always produce all the commands affecting a single table together, so I don't think you can actually get the desired results --- certainly it would be a nontrivial amount of work to get any useful behavior like that. Ah, quite true. I withdraw my comments then. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
