[PATCHES] Fix for VACUUM in psql autocommit off
In current cvs (as in version 7.4.5), VACUUM does not work at all in autocommit=off mode. That is, because psql does not know that VACUUM cannot be performed inside an transaction. Even if you do ROLLBACK; VACUUM; it will internally issue a ROLLBACK; BEGIN; VACUUM; I consider this a bug and suggest the attached fix. is_transact_command() in src/bin/psql/common.c is used to determine if a command is a transaction modifying command. The diff just adds "vacuum" to those commands, so that psql will not issue a BEGIN before a VACUUM. Best Regards, Michael Paesold psql-autocommit-vacuum.diff Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Fix for VACUUM in psql autocommit off
"Michael Paesold" <[EMAIL PROTECTED]> writes: > In current cvs (as in version 7.4.5), VACUUM does not work at all in > autocommit=off mode. That is, because psql does not know that VACUUM cannot > be performed inside an transaction. > I consider this a bug and suggest the attached fix. If we're going to do that, we should also include the other statements that disallow execution in a transaction, and we should rename is_transact_command to something more appropriate (not to mention fix its comments). A quick grep shows /home/postgres/pgsql/src/backend/commands/dbcommands.c: 95: PreventTransactionChain((void *) stmt, "CREATE DATABASE"); /home/postgres/pgsql/src/backend/commands/dbcommands.c: 497: PreventTransactionChain((void *) dbname, "DROP DATABASE"); /home/postgres/pgsql/src/backend/commands/cluster.c: 175: PreventTransactionChain((void *) stmt, "CLUSTER"); /home/postgres/pgsql/src/backend/commands/indexcmds.c: 995: PreventTransactionChain((void *) dbname, "REINDEX DATABASE"); /home/postgres/pgsql/src/backend/commands/vacuum.c: 262: PreventTransactionChain((void *) vacstmt, stmttype); /home/postgres/pgsql/src/backend/commands/tablespace.c: 221: PreventTransactionChain((void *) stmt, "CREATE TABLESPACE"); /home/postgres/pgsql/src/backend/commands/tablespace.c: 407: PreventTransactionChain((void *) stmt, "DROP TABLESPACE"); Handling the multi-keyword cases is going to take a nontrivial increment of functionality. Perhaps while we're at it, we could teach this code about nested /* comments ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Fix for VACUUM in psql autocommit off
Tom Lane wrote: > If we're going to do that, we should also include the other statements > that disallow execution in a transaction, and we should rename > is_transact_command to something more appropriate (not to mention fix > its comments). A quick grep shows > > PreventTransactionChain((void *) stmt, "CREATE DATABASE"); > PreventTransactionChain((void *) dbname, "DROP DATABASE"); > PreventTransactionChain((void *) stmt, "CLUSTER"); > PreventTransactionChain((void *) dbname, "REINDEX DATABASE"); > PreventTransactionChain((void *) vacstmt, stmttype); > PreventTransactionChain((void *) stmt, "CREATE TABLESPACE"); > PreventTransactionChain((void *) stmt, "DROP TABLESPACE"); > Handling the multi-keyword cases is going to take a nontrivial increment > of functionality. Perhaps while we're at it, we could teach this code > about nested /* comments ... Currently there is no need for nested comments, because those are only single word queries. Or do I not understand what you mean by nested comments? (There is code for ignore /* .. */ before the first keyword.) Any suggestion how to that? I can think of a way myself, but it may not be the best, as I don't consider C my natural language. I can try, or does anyone else feel inclined to fix this? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Fix for VACUUM in psql autocommit off
"Michael Paesold" <[EMAIL PROTECTED]> writes: > Or do I not understand what you mean by nested > comments? (There is code for ignore /* .. */ before the first keyword.) Per SQL spec, the backend thinks that /* .. */ nests: regression=# /* some /* comment */ comment */ select 1; ?column? -- 1 (1 row) As it stands, is_transact_command will get confused by this. > Any suggestion how to that? I can think of a way myself, but it may not be > the best, as I don't consider C my natural language. I can try, or does > anyone else feel inclined to fix this? I'd split out the code that locates "the next keyword" into a separate function that could be called twice. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Fix for VACUUM in psql autocommit off
Tom Lane wrote: > "Michael Paesold" <[EMAIL PROTECTED]> writes: > > Or do I not understand what you mean by nested > > comments? (There is code for ignore /* .. */ before the first keyword.) > > Per SQL spec, the backend thinks that /* .. */ nests: > > regression=# /* some /* comment */ comment */ select 1; > ?column? > -- > 1 > (1 row) > > As it stands, is_transact_command will get confused by this. Ok, my patch does not solve that, but I will have a look at it. Tomorrow it will be then, since it's already late here in Europe. > > Any suggestion how to that? I can think of a way myself, but it may not be > > the best, as I don't consider C my natural language. I can try, or does > > anyone else feel inclined to fix this? > > I'd split out the code that locates "the next keyword" into a separate > function that could be called twice. I have read this mail after making the attached patch, so I have used a little different approach. I have moved the code to skip over whitespace and comments into it's own function. The code is not perfect, but it works. By not perfect I mean, it also returns true, if you say "REINDEX TABLESPACE", but this does not exist anyways. Please have a look. Regards, Michael Paesold psql-autocommit.diff Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] documentation fixes
The attached patch makes some minor improvements and cleanup to the docs. Patch applied to HEAD. -Neil Index: doc/src/sgml/ref/create_operator.sgml === RCS file: /Users/neilc/local/cvs/pgsql-server/doc/src/sgml/ref/create_operator.sgml,v retrieving revision 1.42 diff -c -r1.42 create_operator.sgml *** doc/src/sgml/ref/create_operator.sgml 25 Jun 2004 21:55:50 - 1.42 --- doc/src/sgml/ref/create_operator.sgml 18 Sep 2004 02:07:29 - *** *** 262,270 !Use DROP OPERATOR to delete user-defined !operators from a database. Use ALTER OPERATOR !to modify operators in a database. --- 262,272 !Use to delete user-defined operators !from a database. Use to modify operators in a !database. Index: doc/src/sgml/ref/deallocate.sgml === RCS file: /Users/neilc/local/cvs/pgsql-server/doc/src/sgml/ref/deallocate.sgml,v retrieving revision 1.5 diff -c -r1.5 deallocate.sgml *** doc/src/sgml/ref/deallocate.sgml 29 Nov 2003 19:51:38 - 1.5 --- doc/src/sgml/ref/deallocate.sgml 18 Sep 2004 02:05:34 - *** *** 71,76 --- 71,85 statement, but it is only for use in embedded SQL. + + + See Also + + + + + +
[PATCHES] small doc patch for postgresql.conf.sample
Bruce, Re-building CVS I noticed that I never submitted this doc-only patch to postgresql.conf.sample. -- Josh Berkus Aglio Database Solutions San Francisco Index: postgresql.conf.sample === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.127 diff -c -r1.127 postgresql.conf.sample *** postgresql.conf.sample 31 Aug 2004 04:53:44 - 1.127 --- postgresql.conf.sample 20 Sep 2004 00:58:28 - *** *** 11,16 --- 11,19 # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # + # Please note that re-commenting values does NOT revert them to the + # default values. + # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. *** *** 18,24 # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use ! # "pg_ctl reload". #--- --- 21,28 # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use ! # "pg_ctl reload". Some settings, such as listen_address, require ! # a full shutdown and restart to take effect. #--- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
