--single-transaction doc clarification
Clarification of when it's not appropriate to use this option.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Attachments:
singletransdocpatch.patchtext/x-patch; charset=UTF-8; name=singletransdocpatch.patchDownload
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.172
diff -c -r1.172 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 23 Oct 2006 18:10:32 -0000 1.172
--- doc/src/sgml/ref/psql-ref.sgml 30 Oct 2006 22:07:39 -0000
***************
*** 472,480 ****
<option>-f</> option, adding this option wraps
<command>BEGIN</>/<command>COMMIT</> around the script to execute it
as a single transaction. This ensures that either all the commands
! complete successfully, or no changes are applied. (However, if the
! script itself uses <command>BEGIN</> or <command>COMMIT</>, this
! option will not have the desired effect!)
</para>
</listitem>
</varlistentry>
--- 472,492 ----
<option>-f</> option, adding this option wraps
<command>BEGIN</>/<command>COMMIT</> around the script to execute it
as a single transaction. This ensures that either all the commands
! complete successfully, or no changes are applied.
! </para>
! <para>
! However, if the script itself uses <command>BEGIN</> or <command>COMMIT</>, or if
! any of the following commands occur in the script, then this option will simply
! cause all changes to fail: <command>CREATE/DROP DATABASE</>,
! <command>CREATE/DROP TABLESPACE</>, <command>CLUSTER</> of multiple tables,
! <command>REINDEX DATABASE</>, <command>VACUUM</>, <command>COMMIT PREPARED</>
! or <command>ROLLBACK PREPARED</>.
! </para>
! <para>
! You are advised to set the following variable when using this option:
! <programlisting>
! \set ON_ERROR_STOP
! </programlisting>
</para>
</listitem>
</varlistentry>
"Simon Riggs" <simon@2ndquadrant.com> writes:
Clarification of when it's not appropriate to use this option.
I think it's a fairly bad idea to try to enumerate the commands that
can't be used in a transaction block here, because there is no way
that we will remember to keep such a list up-to-date. Why not just
say "It won't work for commands that can't be used in a transaction
block"?
regards, tom lane
On Mon, 2006-10-30 at 17:32 -0500, Tom Lane wrote:
"Simon Riggs" <simon@2ndquadrant.com> writes:
Clarification of when it's not appropriate to use this option.
I think it's a fairly bad idea to try to enumerate the commands that
can't be used in a transaction block here, because there is no way
that we will remember to keep such a list up-to-date. Why not just
say "It won't work for commands that can't be used in a transaction
block"?
I agree, but such a comment immediately begs the question: What is the
list of commands this applies to? I was surprised how long a list it was
myself, hence the patch.
(Checks)...The man pages for VACUUM, CREATE TABLESPACE, CLUSTER and
REINDEX DATABASE don't mention they are not allowed inside a transaction
block at all, so I'm not sure if doing it the other way around helps
with maintaining docs...
I prefer useful info rather than hidden gotchas, even if the list might
possibly be < 100% exactly correct. At least we can say we tried.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
On Mon, 2006-10-30 at 22:56 +0000, Simon Riggs wrote:
The man pages for VACUUM, CREATE TABLESPACE, CLUSTER and
REINDEX DATABASE don't mention they are not allowed inside a transaction
block at all
That should be fixed, I think. Once that is done, I think it's
sufficient to just say that --single-transaction won't work for commands
that can't be executed in a transaction block.
-Neil
Neil Conway <neilc@samurai.com> writes:
On Mon, 2006-10-30 at 22:56 +0000, Simon Riggs wrote:
The man pages for VACUUM, CREATE TABLESPACE, CLUSTER and
REINDEX DATABASE don't mention they are not allowed inside a transaction
block at all
That should be fixed, I think. Once that is done, I think it's
sufficient to just say that --single-transaction won't work for commands
that can't be executed in a transaction block.
That's what makes sense to me, too.
regards, tom lane
On Mon, 2006-10-30 at 19:28 -0500, Tom Lane wrote:
Neil Conway <neilc@samurai.com> writes:
On Mon, 2006-10-30 at 22:56 +0000, Simon Riggs wrote:
The man pages for VACUUM, CREATE TABLESPACE, CLUSTER and
REINDEX DATABASE don't mention they are not allowed inside a transaction
block at allThat should be fixed, I think. Once that is done, I think it's
sufficient to just say that --single-transaction won't work for commands
that can't be executed in a transaction block.That's what makes sense to me, too.
As requested.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Attachments:
notintransblock.patchtext/x-patch; charset=UTF-8; name=notintransblock.patchDownload
Index: doc/src/sgml/ref/cluster.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/cluster.sgml,v
retrieving revision 1.36
diff -c -r1.36 cluster.sgml
*** doc/src/sgml/ref/cluster.sgml 16 Sep 2006 00:30:17 -0000 1.36
--- doc/src/sgml/ref/cluster.sgml 31 Oct 2006 01:05:48 -0000
***************
*** 60,67 ****
in the
current database that the calling user owns, or all tables if called
by a superuser. (Never-clustered tables are not included.) This
! form of <command>CLUSTER</command> cannot be called from inside a
! transaction or function.
</para>
<para>
--- 60,67 ----
in the
current database that the calling user owns, or all tables if called
by a superuser. (Never-clustered tables are not included.) This
! form of <command>CLUSTER</command> cannot be executed inside a transaction
! block.
</para>
<para>
Index: doc/src/sgml/ref/create_tablespace.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_tablespace.sgml,v
retrieving revision 1.6
diff -c -r1.6 create_tablespace.sgml
*** doc/src/sgml/ref/create_tablespace.sgml 16 Sep 2006 00:30:17 -0000 1.6
--- doc/src/sgml/ref/create_tablespace.sgml 31 Oct 2006 01:05:48 -0000
***************
*** 95,100 ****
--- 95,105 ----
<para>
Tablespaces are only supported on systems that support symbolic links.
</para>
+
+ <para>
+ <command>CREATE TABLESPACE</> cannot be executed inside a transaction
+ block.
+ </para>
</refsect1>
<refsect1>
Index: doc/src/sgml/ref/drop_tablespace.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/drop_tablespace.sgml,v
retrieving revision 1.4
diff -c -r1.4 drop_tablespace.sgml
*** doc/src/sgml/ref/drop_tablespace.sgml 16 Sep 2006 00:30:18 -0000 1.4
--- doc/src/sgml/ref/drop_tablespace.sgml 31 Oct 2006 01:05:49 -0000
***************
*** 67,72 ****
--- 67,81 ----
</refsect1>
<refsect1>
+ <title>Notes</title>
+
+ <para>
+ <command>DROP TABLESPACE</> cannot be executed inside a transaction block.
+ </para>
+ </refsect1>
+
+
+ <refsect1>
<title>Examples</title>
<para>
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.172
diff -c -r1.172 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 23 Oct 2006 18:10:32 -0000 1.172
--- doc/src/sgml/ref/psql-ref.sgml 31 Oct 2006 01:05:50 -0000
***************
*** 472,480 ****
<option>-f</> option, adding this option wraps
<command>BEGIN</>/<command>COMMIT</> around the script to execute it
as a single transaction. This ensures that either all the commands
! complete successfully, or no changes are applied. (However, if the
! script itself uses <command>BEGIN</> or <command>COMMIT</>, this
! option will not have the desired effect!)
</para>
</listitem>
</varlistentry>
--- 472,489 ----
<option>-f</> option, adding this option wraps
<command>BEGIN</>/<command>COMMIT</> around the script to execute it
as a single transaction. This ensures that either all the commands
! complete successfully, or no changes are applied.
! </para>
! <para>
! However, if the script itself uses <command>BEGIN</>, <command>COMMIT</> or any
! commands that cannot be executed inside a transaction block then this option
! will simply cause all changes to fail. See the individual command manual pages.
! </para>
! <para>
! You are advised to set the following variable when using this option:
! <programlisting>
! \set ON_ERROR_STOP
! </programlisting>
</para>
</listitem>
</varlistentry>
Index: doc/src/sgml/ref/reindex.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/reindex.sgml,v
retrieving revision 1.33
diff -c -r1.33 reindex.sgml
*** doc/src/sgml/ref/reindex.sgml 16 Sep 2006 00:30:19 -0000 1.33
--- doc/src/sgml/ref/reindex.sgml 31 Oct 2006 01:05:50 -0000
***************
*** 106,112 ****
<para>
Recreate all indexes within the current database.
Indexes on shared system catalogs are skipped except in stand-alone mode
! (see below).
</para>
</listitem>
</varlistentry>
--- 106,113 ----
<para>
Recreate all indexes within the current database.
Indexes on shared system catalogs are skipped except in stand-alone mode
! (see below). This form of <command>REINDEX</command> cannot be executed
! inside a transaction block.
</para>
</listitem>
</varlistentry>
***************
*** 118,123 ****
--- 119,126 ----
Recreate all indexes on system catalogs within the current database.
Indexes on user tables are not processed. Also, indexes on shared
system catalogs are skipped except in stand-alone mode (see below).
+ This form of <command>REINDEX</command> cannot be executed inside a
+ transaction block.
</para>
</listitem>
</varlistentry>
Index: doc/src/sgml/ref/vacuum.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/vacuum.sgml,v
retrieving revision 1.41
diff -c -r1.41 vacuum.sgml
*** doc/src/sgml/ref/vacuum.sgml 16 Sep 2006 00:30:20 -0000 1.41
--- doc/src/sgml/ref/vacuum.sgml 31 Oct 2006 01:05:50 -0000
***************
*** 156,161 ****
--- 156,165 ----
<title>Notes</title>
<para>
+ <command>VACUUM</> cannot be executed inside a transaction block.
+ </para>
+
+ <para>
We recommend that active production databases be
vacuumed frequently (at least nightly), in order to
remove expired rows. After adding or deleting a large number
On Tue, 2006-10-31 at 01:07 +0000, Simon Riggs wrote:
As requested.
Applied, thanks for the patch.
I didn't apply the ON_ERROR_STOP addition: IMHO it's not very useful to
say that doing something is "advised" without explaining why it is
wise/useful/necessary. If we want to include this point, can you add
some elaboration on why/when ON_ERROR_STOP should be used with
--single-transaction?
-Neil
Neil Conway <neilc@samurai.com> writes:
On Tue, 2006-10-31 at 01:07 +0000, Simon Riggs wrote:
As requested.
Applied, thanks for the patch.
This patch converted a correct statement into a lie: there is not
anything that will cause begin/commit in a script file to fail just
because you wrapped begin/commit around them. I rewrote the text to
If the script itself uses <command>BEGIN</>, <command>COMMIT</>,
or <command>ROLLBACK</>, this option will not have the desired
effects.
Also, if the script contains any command that cannot be executed
inside a transaction block, specifying this option will cause that
command (and hence the whole transaction) to fail.
regards, tom lane
On Mon, Oct 30, 2006 at 07:18:04PM -0500, Neil Conway wrote:
On Mon, 2006-10-30 at 22:56 +0000, Simon Riggs wrote:
The man pages for VACUUM, CREATE TABLESPACE, CLUSTER and
REINDEX DATABASE don't mention they are not allowed inside a transaction
block at allThat should be fixed, I think. Once that is done, I think it's
sufficient to just say that --single-transaction won't work for commands
that can't be executed in a transaction block.
"What commands aren't allowed in a transaction?" is still a logical
question to ask though, so it would be nice if we had such a list
hanging around.
Is there a standard way these commands test to see if they're in a
transaction block? If there is, perhaps something could be created that
would pull that info out of the code so that we didn't have to maintain
the list by hand. It might also be possible to do this with some SGML
magic.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)