codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>46.7. Explicit Subtransactions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpython-database.html" title="46.6. Database Access" /><link rel="next" href="plpython-transactions.html" title="46.8. Transaction Management" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">46.7. Explicit Subtransactions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpython-database.html" title="46.6. Database Access">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><th width="60%" align="center">Chapter 46. PL/Python — Python Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpython-transactions.html" title="46.8. Transaction Management">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPYTHON-SUBTRANSACTION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">46.7. Explicit Subtransactions <a href="#PLPYTHON-SUBTRANSACTION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpython-subtransaction.html#PLPYTHON-SUBTRANSACTION-CONTEXT-MANAGERS">46.7.1. Subtransaction Context Managers</a></span></dt></dl></div><p>3   Recovering from errors caused by database access as described in4   <a class="xref" href="plpython-database.html#PLPYTHON-TRAPPING" title="46.6.2. Trapping Errors">Section 46.6.2</a> can lead to an undesirable5   situation where some operations succeed before one of them fails,6   and after recovering from that error the data is left in an7   inconsistent state.  PL/Python offers a solution to this problem in8   the form of explicit subtransactions.9  </p><div class="sect2" id="PLPYTHON-SUBTRANSACTION-CONTEXT-MANAGERS"><div class="titlepage"><div><div><h3 class="title">46.7.1. Subtransaction Context Managers <a href="#PLPYTHON-SUBTRANSACTION-CONTEXT-MANAGERS" class="id_link">#</a></h3></div></div></div><p>10    Consider a function that implements a transfer between two11    accounts:12</p><pre class="programlisting">13CREATE FUNCTION transfer_funds() RETURNS void AS $$14try:15    plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")16    plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")17except plpy.SPIError as e:18    result = "error transferring funds: %s" % e.args19else:20    result = "funds transferred correctly"21plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])22plpy.execute(plan, [result])23$$ LANGUAGE plpython3u;24</pre><p>25    If the second <code class="literal">UPDATE</code> statement results in an26    exception being raised, this function will report the error, but27    the result of the first <code class="literal">UPDATE</code> will28    nevertheless be committed.  In other words, the funds will be29    withdrawn from Joe's account, but will not be transferred to30    Mary's account.31   </p><p>32    To avoid such issues, you can wrap your33    <code class="literal">plpy.execute</code> calls in an explicit34    subtransaction.  The <code class="literal">plpy</code> module provides a35    helper object to manage explicit subtransactions that gets created36    with the <code class="literal">plpy.subtransaction()</code> function.37    Objects created by this function implement the38    <a class="ulink" href="https://docs.python.org/library/stdtypes.html#context-manager-types" target="_top">39    context manager interface</a>.  Using explicit subtransactions40    we can rewrite our function as:41</p><pre class="programlisting">42CREATE FUNCTION transfer_funds2() RETURNS void AS $$43try:44    with plpy.subtransaction():45        plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")46        plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")47except plpy.SPIError as e:48    result = "error transferring funds: %s" % e.args49else:50    result = "funds transferred correctly"51plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])52plpy.execute(plan, [result])53$$ LANGUAGE plpython3u;54</pre><p>55    Note that the use of <code class="literal">try</code>/<code class="literal">except</code> is still56    required.  Otherwise the exception would propagate to the top of57    the Python stack and would cause the whole function to abort with58    a <span class="productname">PostgreSQL</span> error, so that the59    <code class="literal">operations</code> table would not have any row60    inserted into it.  The subtransaction context manager does not61    trap errors, it only assures that all database operations executed62    inside its scope will be atomically committed or rolled back.  A63    rollback of the subtransaction block occurs on any kind of64    exception exit, not only ones caused by errors originating from65    database access.  A regular Python exception raised inside an66    explicit subtransaction block would also cause the subtransaction67    to be rolled back.68   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpython-database.html" title="46.6. Database Access">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpython-transactions.html" title="46.8. Transaction Management">Next</a></td></tr><tr><td width="40%" align="left" valign="top">46.6. Database Access </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 46.8. Transaction Management</td></tr></table></div></body></html>