On Wed, 8 Mar 2017 16:49:33 +0100
Pavel Stehule <pavel.steh...@gmail.com> wrote:
>
> I did a review of this patch
>
I'm attaching new version of patch with the issues pointed by you fixed.
>
> 4. A documentation is good - although I am not sure if it is well
> structured - is <sect2> level necessary? Probably there will not be
> any other similar command.
Removed <sect2>. Although it is questionable - now there is no
subsection with name of the command in the title. But surrounding
section describes only one command,.
> 5. There are a basic regress tests, and all tests passed, but I miss a
> path, where subtransaction is commited - now rollback is every time
Added test with successful subtransaction commit. Just to be sure it is
really commited.
> 6. The code has some issues with white chars
Fixed where I've found it. Now, hopefully my code uses same identation
rules as other pltcl.c
> 7. I don't understand why TopMemoryContext is used there? Maybe some
> already used context should be there.
>
> +<->BeginInternalSubTransaction(NULL);
> +<->MemoryContextSwitchTo(TopTransactionContext);
> +<->
It turns out that was really an error in the first version of patch.
This context manipulation was copied from PL/Python context manager by
mistake.
PL/Python changes to TopTransactionContext in order to manage
stack of subtransaction data (which we don't need in Tcl, because we
can use C language stack and store neccessary data in function
automatic variables. Really python doesn't need this stack to, because
Python context manager is a python language object and can keep state
inside it).
Although we still need to keep MemoryContext and ResourceOwner and
restore them upon transaction finish.
With best regards, Victor
--
Victor Wagner <vi...@wagner.pp.ru>diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index ad216dd..87bc4ad 100644
--- a/doc/src/sgml/pltcl.sgml
+++ b/doc/src/sgml/pltcl.sgml
@@ -901,6 +901,79 @@ if {[catch { spi_exec $sql_command }]} {
is a global variable.)
</para>
</sect1>
+ <sect1 id="pltcl-subtransaction">
+ <title>Explicit Subtransactions</title>
+ <para>
+ Recovering from errors caused by database access as described in
+ <xref linkend="pltcl-error-handling"> can lead to an undesirable
+ situation where some operations succeed before one of them fails,
+ and after recovering from that error the data is left in an
+ inconsistent state. PL/Tcl offers a solution to this problem in
+ the form of explicit subtransactions.
+ </para>
+ <para>
+ Consider a function that implements a transfer between two
+ accounts:
+<programlisting>
+CREATE FUNCTION transfer_funds() RETURNS void AS $$
+if [catch {
+ spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
+ spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
+} errormsg] {
+ set result [format "error transferring funds: %s" $errormsg ]
+} else {
+ set result "funds transferred correctly"
+}
+set plan [spi_prepare "INSERT INTO operations (result) VALUES ($1)"]
+spi_execp -count 1 $plan, [list $result)
+$$ LANGUAGE pltclu;
+</programlisting>
+ If the second <literal>UPDATE</literal> statement results in an
+ exception being raised, this function will report the error, but
+ the result of the first <literal>UPDATE</literal> will
+ nevertheless be committed. In other words, the funds will be
+ withdrawn from Joe's account, but will not be transferred to
+ Mary's account.
+ </para>
+ <para>
+ To avoid such issues, you can wrap your
+ <literal>spi_exec</literal> calls in an explicit
+ subtransaction. The PL/Tcl provides a
+ commmand <literal>subtransaction</literal> to manage explicit
+ subtransactions.
+ Using explicit subtransactions
+ we can rewrite our function as:
+<programlisting>
+CREATE FUNCTION transfer_funds2() RETURNS void AS $$
+if [catch {
+ subtransaction {
+ spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
+ spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
+ }
+ } errormsg] {
+ set result [format "error transferring funds: %s" $errormsg]
+ } else {
+ set result "funds transferred correctly"
+ }
+ set plan [spi_prepare "INSERT INTO operations (result) VALUES ($1)"]
+ spi_execp $plan, [list $result]
+$$ LANGUAGE pltclu;
+</programlisting>
+ Note that the use of <literal>catch</literal> is still
+ required. Otherwise the exception would propagate to the top of
+ the stack and would cause the whole function to abort with
+ a <productname>PostgreSQL</productname> error, so that the
+ <literal>operations</literal> table would not have any row
+ inserted into it. The <literal>subtransaction</literal> command does not
+ trap errors, it only assures that all database operations executed
+ inside its scope will be atomically committed or rolled back. A
+ rollback of the subtransaction block occurs on any kind of
+ exception exit, not only ones caused by errors originating from
+ database access. A regular Tcl exception raised inside an
+ explicit subtransaction block would also cause the subtransaction
+ to be rolled back.
+ </para>
+ </sect1>
<sect1 id="pltcl-config">
<title>PL/Tcl Configuration</title>
diff --git a/src/pl/tcl/Makefile b/src/pl/tcl/Makefile
index 1096c4f..b6b6b19 100644
--- a/src/pl/tcl/Makefile
+++ b/src/pl/tcl/Makefile
@@ -28,7 +28,7 @@ DATA = pltcl.control pltcl--1.0.sql pltcl--unpackaged--1.0.sql \
pltclu.control pltclu--1.0.sql pltclu--unpackaged--1.0.sql
REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-extension=pltcl
-REGRESS = pltcl_setup pltcl_queries pltcl_start_proc pltcl_unicode
+REGRESS = pltcl_setup pltcl_queries pltcl_start_proc pltcl_unicode pltcl_subxact
# Tcl on win32 ships with import libraries only for Microsoft Visual C++,
# which are not compatible with mingw gcc. Therefore we need to build a
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 2cf7e66..8d498ae 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -323,6 +323,8 @@ static HeapTuple pltcl_build_tuple_result(Tcl_Interp *interp,
pltcl_call_state *call_state);
static void pltcl_init_tuple_store(pltcl_call_state *call_state);
+static int pltcl_subtransaction(ClientData cdata, Tcl_Interp *interp,
+ int objc, Tcl_Obj *const objv[]);
/*
* Hack to override Tcl's builtin Notifier subsystem. This prevents the
@@ -516,7 +518,8 @@ pltcl_init_interp(pltcl_interp_desc *interp_desc, Oid prolang, bool pltrusted)
pltcl_SPI_execute_plan, NULL, NULL);
Tcl_CreateObjCommand(interp, "spi_lastoid",
pltcl_SPI_lastoid, NULL, NULL);
-
+ Tcl_CreateObjCommand(interp, "subtransaction",
+ pltcl_subtransaction, NULL,NULL);
/************************************************************
* Call the appropriate start_proc, if there is one.
*
@@ -3114,3 +3117,44 @@ pltcl_init_tuple_store(pltcl_call_state *call_state)
CurrentResourceOwner = oldowner;
MemoryContextSwitchTo(oldcxt);
}
+
+/*
+ * pltcl_subtransaction - implements tcl level subtransaction block
+ * Called with exactly one argument - piece of Tcl Code, and executes
+ * this code inside subtransaction.
+ * Rolls subtransaction back if Tcl_EvalEx returns TCL_ERROR
+ */
+static int
+pltcl_subtransaction(ClientData cdata, Tcl_Interp *interp,
+ int objc, Tcl_Obj *const objv[])
+{
+ /* Save resource owner and memory context in the local vars */
+ ResourceOwner oldowner = CurrentResourceOwner;
+ MemoryContext oldcontext = CurrentMemoryContext;
+ int retcode;
+ if (objc != 2)
+ {
+ Tcl_WrongNumArgs(interp,1,objv,"command");
+ return TCL_ERROR;
+ }
+
+ BeginInternalSubTransaction(NULL);
+
+ retcode = Tcl_EvalObjEx(interp, objv[1],0);
+
+ if (retcode == TCL_ERROR)
+ {
+ /* Roollback the subtransaction */
+ RollbackAndReleaseCurrentSubTransaction();
+ }
+ else
+ {
+ /* Commit the subtransaction */
+ ReleaseCurrentSubTransaction();
+ }
+ /* Restore resource owner and memory context
+ In case they were changed inside subtransaction */
+ CurrentResourceOwner = oldowner;
+ MemoryContextSwitchTo(oldcontext);
+ return retcode;
+}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
