Re: [HACKERS] pl/python explicit subtransactions

2011-02-27 Thread Peter Eisentraut
Committed.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-26 Thread Jan Urbański
- Original message -
 On Wed, Feb 9, 2011 at 5:22 PM, Peter Eisentraut pete...@gmx.net wrote:
  On tis, 2011-02-08 at 00:32 -0500, Steve Singer wrote:
   The documentation could probably still use more word-smithing but
   that can happen later.  I'm marking this as ready for a committer.
  
  Is it necessarily a good idea that an explicit subtransaction disables
  the implicit sub-subtransactions?  It might be conceivable that you'd
  still want to do some try/catch within explicit subtransactions.
 
 Is this still an open question, or what is the remaining issue that
 needs to be addressed with regards to this patch?

The docs are included in the latest patch, and it turned out that disabling 
implicit subxacts inside explicit subxacts is not a good idea, so it's been 
fixed in the last patch. There are no unresolved issues AFAICT.

Jan

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-26 Thread Robert Haas
2011/2/26 Jan Urbański wulc...@wulczer.org:
 The docs are included in the latest patch, and it turned out that disabling 
 implicit subxacts inside explicit subxacts is not a good idea, so it's been 
 fixed in the last patch. There are no unresolved issues AFAICT.

OK.  Peter, are you planning to commit this?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-26 Thread Peter Eisentraut
On lör, 2011-02-26 at 09:49 -0500, Robert Haas wrote:
 OK.  Peter, are you planning to commit this?

Yes.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-25 Thread Robert Haas
On Wed, Feb 9, 2011 at 5:22 PM, Peter Eisentraut pete...@gmx.net wrote:
 On tis, 2011-02-08 at 00:32 -0500, Steve Singer wrote:
 On 11-02-06 11:40 AM, Jan Urbański wrote:

  PFA an updated patch with documentation.

  Yeah, changed them.

 Those changes look fine.  The tests now pass.

 I've attached a new version of the patch that fixes a few typos/wording
 issues I saw in the documentation.  I also changed the link to the
 python reference manual section on context managers. I think it is
 better to link to that versus the original PEP.

 The documentation could probably still use more word-smithing but that
 can happen later.  I'm marking this as ready for a committer.

 Is it necessarily a good idea that an explicit subtransaction disables
 the implicit sub-subtransactions?  It might be conceivable that you'd
 still want to do some try/catch within explicit subtransactions.

Is this still an open question, or what is the remaining issue that
needs to be addressed with regards to this patch?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-12 Thread Jan Urbański
On 11/02/11 17:22, Steve Singer wrote:
 On 11-02-10 05:20 AM, Jan Urbański wrote:

 D'oh, I was thinking about whether it's safe to skip the internal
 subxact if you're in an implicit one and somehow I always convinced
 myself that since you eventually close the explicit one, it is.

 Obviously my testing wasn't enough :( Attaching an updated patch with
 improved docs incorporating Steve's fixes, and fixes  tests for not
 statring the implicit subxact. That actually makes the patch a bit
 smaller ;) OTOH I had to remove the section from the docs that claimed
 performance improvement due to only starting the explicit subxact...

 
 This version of the patch looks fine to me and seems to work as expected.

Thanks,

attached is a version merged with master.

Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..87be8c2 100644
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*** $$ LANGUAGE plpythonu;
*** 943,949 
  
/sect2
  
!   sect2
 titleTrapping Errors/title
  
 para
--- 943,949 
  
/sect2
  
!   sect2 id=plpython-trapping
 titleTrapping Errors/title
  
 para
*** $$ LANGUAGE plpythonu;
*** 968,973 
--- 968,1089 
 /para
/sect2
   /sect1
+ 
+  sect1 id=plpython-subtransaction
+   titleExplicit subtransactions/title
+   para
+ Recovering from errors caused by database access as described
+ in xref linkend=plpython-trapping 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/Python offers
+ a solution to this problem in the form of explicit subtransactions.
+   /para
+ 
+   sect2
+titleSubtransaction context managers/title
+para
+  Consider a function that implements a transfer between two accounts:
+ programlisting
+ CREATE FUNCTION transfer_funds() RETURNS void AS $$
+ try:
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  If the second literalUPDATE/literal statement results in an exception
+  being raised, this function will report the error, but the result of the
+  first literalUPDATE/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 literalplpy.execute/literal
+  calls in an explicit subtransaction. The literalplpy/literal module
+  provides a helper object to manage explicit subtransactions that gets
+  created with the literalplpy.subtransaction()/literal function.
+  Objects created by this function implement the
+  ulink url=http://docs.python.org/library/stdtypes.html#context-manager-types;
+  context manager interface/ulink. Using explicit subtransactions we can
+  rewrite our function as:
+ programlisting
+ CREATE FUNCTION transfer_funds2() RETURNS void AS $$
+ try:
+ with plpy.subtransaction():
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  Note that the use of literaltry/catch/literal is still
+  required. Otherwise the exception would propagate to the top of the Python
+  stack and would cause the whole function to abort with
+  a productnamePostgreSQL/productname error.
+  The literaloperations/literal table would not have any row inserted
+  into it. The subtransaction context manager 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 occurrs on any kind of exception exit, not only ones caused by
+  errors originating from database access. A regular Python exception raised
+  inside an explicit subtransaction block would also cause the
+  subtransaction to be rolled back.
+/para
+   /sect2
+ 
+   sect2
+titleOlder Python versions/title
+ 
+para
+  Context managers syntax using the literalwith/literal keyword is
+  available by default in Python 2.6. If using 

Re: [HACKERS] pl/python explicit subtransactions

2011-02-11 Thread Steve Singer

On 11-02-10 05:20 AM, Jan Urbański wrote:


D'oh, I was thinking about whether it's safe to skip the internal
subxact if you're in an implicit one and somehow I always convinced
myself that since you eventually close the explicit one, it is.

Obviously my testing wasn't enough :( Attaching an updated patch with
improved docs incorporating Steve's fixes, and fixes  tests for not
statring the implicit subxact. That actually makes the patch a bit
smaller ;) OTOH I had to remove the section from the docs that claimed
performance improvement due to only starting the explicit subxact...



This version of the patch looks fine to me and seems to work as expected.



Cheers,
Jan







Re: [HACKERS] pl/python explicit subtransactions

2011-02-10 Thread Jan Urbański
On 10/02/11 01:26, Steve Singer wrote:
 On 11-02-09 05:22 PM, Peter Eisentraut wrote:
 On tis, 2011-02-08 at 00:32 -0500, Steve Singer wrote:
 Is it necessarily a good idea that an explicit subtransaction disables
 the implicit sub-subtransactions?  It might be conceivable that you'd
 still want to do some try/catch within explicit subtransactions.

 
 I had tested nested subtransactions but not a normal try/catch within a
 subtransaction.  That sounds reasonable to allow.
 
 Unfortunately it leads to:
 
 [crash]

D'oh, I was thinking about whether it's safe to skip the internal
subxact if you're in an implicit one and somehow I always convinced
myself that since you eventually close the explicit one, it is.

Obviously my testing wasn't enough :( Attaching an updated patch with
improved docs incorporating Steve's fixes, and fixes  tests for not
statring the implicit subxact. That actually makes the patch a bit
smaller ;) OTOH I had to remove the section from the docs that claimed
performance improvement due to only starting the explicit subxact...

Cheers,
Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..87be8c2 100644
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*** $$ LANGUAGE plpythonu;
*** 943,949 
  
/sect2
  
!   sect2
 titleTrapping Errors/title
  
 para
--- 943,949 
  
/sect2
  
!   sect2 id=plpython-trapping
 titleTrapping Errors/title
  
 para
*** $$ LANGUAGE plpythonu;
*** 968,973 
--- 968,1089 
 /para
/sect2
   /sect1
+ 
+  sect1 id=plpython-subtransaction
+   titleExplicit subtransactions/title
+   para
+ Recovering from errors caused by database access as described
+ in xref linkend=plpython-trapping 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/Python offers
+ a solution to this problem in the form of explicit subtransactions.
+   /para
+ 
+   sect2
+titleSubtransaction context managers/title
+para
+  Consider a function that implements a transfer between two accounts:
+ programlisting
+ CREATE FUNCTION transfer_funds() RETURNS void AS $$
+ try:
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  If the second literalUPDATE/literal statement results in an exception
+  being raised, this function will report the error, but the result of the
+  first literalUPDATE/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 literalplpy.execute/literal
+  calls in an explicit subtransaction. The literalplpy/literal module
+  provides a helper object to manage explicit subtransactions that gets
+  created with the literalplpy.subtransaction()/literal function.
+  Objects created by this function implement the
+  ulink url=http://docs.python.org/library/stdtypes.html#context-manager-types;
+  context manager interface/ulink. Using explicit subtransactions we can
+  rewrite our function as:
+ programlisting
+ CREATE FUNCTION transfer_funds2() RETURNS void AS $$
+ try:
+ with plpy.subtransaction():
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  Note that the use of literaltry/catch/literal is still
+  required. Otherwise the exception would propagate to the top of the Python
+  stack and would cause the whole function to abort with
+  a productnamePostgreSQL/productname error.
+  The literaloperations/literal table would not have any row inserted
+  into it. The subtransaction context manager 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 occurrs on any kind of exception exit, not only ones caused by
+  errors originating from database access. A regular Python exception raised
+  inside an 

Re: [HACKERS] pl/python explicit subtransactions

2011-02-09 Thread Peter Eisentraut
On tis, 2011-02-08 at 00:32 -0500, Steve Singer wrote:
 On 11-02-06 11:40 AM, Jan Urbański wrote:
 
  PFA an updated patch with documentation.
 
  Yeah, changed them.
 
 Those changes look fine.  The tests now pass.
 
 I've attached a new version of the patch that fixes a few typos/wording 
 issues I saw in the documentation.  I also changed the link to the 
 python reference manual section on context managers. I think it is 
 better to link to that versus the original PEP.
 
 The documentation could probably still use more word-smithing but that 
 can happen later.  I'm marking this as ready for a committer.

Is it necessarily a good idea that an explicit subtransaction disables
the implicit sub-subtransactions?  It might be conceivable that you'd
still want to do some try/catch within explicit subtransactions.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-09 Thread Steve Singer

On 11-02-09 05:22 PM, Peter Eisentraut wrote:

On tis, 2011-02-08 at 00:32 -0500, Steve Singer wrote:

On 11-02-06 11:40 AM, Jan Urbański wrote:


PFA an updated patch with documentation.
Yeah, changed them.

Those changes look fine.  The tests now pass.

I've attached a new version of the patch that fixes a few typos/wording
issues I saw in the documentation.  I also changed the link to the
python reference manual section on context managers. I think it is
better to link to that versus the original PEP.

The documentation could probably still use more word-smithing but that
can happen later.  I'm marking this as ready for a committer.

Is it necessarily a good idea that an explicit subtransaction disables
the implicit sub-subtransactions?  It might be conceivable that you'd
still want to do some try/catch within explicit subtransactions.




I had tested nested subtransactions but not a normal try/catch within a 
subtransaction.  That sounds reasonable to allow.


Unfortunately it leads to:


test=# create table foo(a int4 primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
foo_pkey for table foo

CREATE TABLE
test=# DO $$
test$# try:
test$#   with plpy.subtransaction():
test$# plpy.execute(insert into foo values(1))
test$# try:
test$#   plpy.execute(insert into foo values(1))
test$# except:
test$#   plpy.notice('inside exception')
test$# except plpy.SPIError:
test$#   f=0
test$# $$ language plpythonu;
TRAP: FailedAssertion(!(afterTriggers-query_depth == 
afterTriggers-depth_stack[my_level]), File: trigger.c, Line: 3846)

NOTICE:  inside exception
CONTEXT:  PL/Python anonymous code block
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-07 Thread Steve Singer

On 11-02-06 11:40 AM, Jan Urbański wrote:


PFA an updated patch with documentation.



Yeah, changed them.


Those changes look fine.  The tests now pass.

I've attached a new version of the patch that fixes a few typos/wording 
issues I saw in the documentation.  I also changed the link to the 
python reference manual section on context managers. I think it is 
better to link to that versus the original PEP.


The documentation could probably still use more word-smithing but that 
can happen later.  I'm marking this as ready for a committer.






Thanks,
Jan


diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..9137ceb 100644
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*** $$ LANGUAGE plpythonu;
*** 943,949 
  
/sect2
  
!   sect2
 titleTrapping Errors/title
  
 para
--- 943,949 
  
/sect2
  
!   sect2 id=plpython-trapping
 titleTrapping Errors/title
  
 para
*** $$ LANGUAGE plpythonu;
*** 969,974 
--- 969,1087 
/sect2
   /sect1
  
+  sect1 id=plpython-subtransaction
+   titleExplicit subtransactions/title
+   para
+ Recovering from errors caused by database access as described
+ in xref linkend=plpython-trapping 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/Python offers
+ a solution to this problem in the form of explicit subtransactions.
+   /para
+ 
+   sect2
+titleSubtransaction context managers/title
+para
+  Consider a function that implements a transfer between two accounts:
+ programlisting
+ CREATE FUNCTION transfer_funds() RETURNS void AS $$
+ try:
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  If the second literalUPDATE/literal statement results in an exception
+  being raised, this function will report the error, but the result of the
+  first literalUPDATE/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 literalplpy.execute/literal
+  calls in an explicit subtransaction. The literalplpy/literal module
+  provides a helper object to manage explicit subtransactions that gets
+  created with the literalplpy.subtransaction()/literal function. 
+  Objects created by this function implement the ulink url=http://www.python.org/doc//current/library/stdtypes.html#context-manager-types;context manager interface/ulink.
+  
+  Using explicit subtransactions we can rewrite our function as:
+ programlisting
+ CREATE FUNCTION transfer_funds2() RETURNS void AS $$
+ try:
+ with plpy.subtransaction():
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  Note that the use of literaltry/catch/literal is still
+  required. Otherwise the exception would propagate to the top of the Python
+  stack and would cause the whole function to abort with
+  a productnamePostgreSQL/productname error.
+  The literaloperations/literal table would not have any row inserted
+  into it. The subtransaction context manager 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 Python exception raised
+  inside an explicit subtransaction block would also cause the
+  subtransaction to be rolled back.
+/para
+para
+  Another reason to use explicit subtransactions is that each
+  time literalplpy.execute/literal or literalplpy.prepare/literal is
+  used, it has to create its own internal subtransaction in order to be able
+  to recover from errors using the literaltry/catch/literal construct. If
+  you explicitly put your database access code in the scope of a
+  

Re: [HACKERS] pl/python explicit subtransactions

2011-02-06 Thread Jan Urbański
On 02/02/11 14:16, Steve Singer wrote:
 On 11-01-27 05:11 PM, Jan Urbański wrote:
 On 23/12/10 15:32, Jan Urbański wrote:
 Here's a patch implementing explicitly starting subtransactions
 mentioned in
 http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
 an incremental patch on top of the spi-in-subxacts patch sent eariler.

 Updated to the spi-in-subxacts version sent earlier.

 [review]
 
 Other than that I don't see any issues.  I am marking this as waiting
 for author since the documentation is still outstanding.

Thanks Steve, I'm writing docs and changing error messages to lowercase
as you suggested and will send an updated patch today.

Jan

PS: Oh, I just saw Peter's suggestion to rename the function from
plpy.subxact() to plpy.subtransaction(). Fine with me, will do that too.

J

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-06 Thread Jan Urbański
On 02/02/11 14:16, Steve Singer wrote:
 On 11-01-27 05:11 PM, Jan Urbański wrote:
 On 23/12/10 15:32, Jan Urbański wrote:
 Here's a patch implementing explicitly starting subtransactions
 mentioned in
 http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
 an incremental patch on top of the spi-in-subxacts patch sent eariler.

 Updated to the spi-in-subxacts version sent earlier.

 
 Submission Review
 -
 
 The patch applies against master.
 Test updates are included.
 
 The patch doesn't included any documentation updates.  The author did
 mention that he'll do these if it looks like the patch is going to be
 accepted.

PFA an updated patch with documentation.

 The plpython_subxact regression test you addded is failing on both
 python3 and 2.4 for me.  It seems to be creating functions with the same
 name twice and the second time is failing with ERROR: function .
 already exists.  I think this is just an issue with your expect files.

The expect files for older Pythons were broken by the change to include
HINT and DETAIL messages when errors are reported from Python. I fixed
them and now the regression tests are passing for me on Python 2.4, 2.6
and 3.1.

 Code Review
 
 
 
 PLy_abort_open_subtransactions(...) line 1215:
 
 ereport(WARNING,
 (errmsg(Forcibly aborting a subtransaction 
 that has not been exited)));
 
 Forcibly should be forcibly (lower case)
 
 Similarly in PLy_subxact_enter and PLy_subxact_exit a few
 PLy_exception_set calls start with an upper case character when I think
 we want it to be lower case.

Yeah, changed them.

Thanks,
Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..9cd4879 100644
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*** $$ LANGUAGE plpythonu;
*** 943,949 
  
/sect2
  
!   sect2
 titleTrapping Errors/title
  
 para
--- 943,949 
  
/sect2
  
!   sect2 id=plpython-trapping
 titleTrapping Errors/title
  
 para
*** CREATE FUNCTION try_adding_joe() RETURNS
*** 966,971 
--- 966,1084 
  $$ LANGUAGE plpythonu;
  /programlisting
 /para
+   /sect2
+  /sect1
+ 
+  sect1 id=plpython-subtransaction
+   titleExplicit subtransactions/title
+   para
+ Recovering from errors caused by database access as described
+ in xref linkend=plpython-trapping 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/Python offers
+ a solution to this problem in the form of explicit subtransactions.
+   /para
+ 
+   sect2
+titleSubtransaction context managers/title
+para
+  Consider a function that implements a transfer between two accounts:
+ programlisting
+ CREATE FUNCTION transfer_funds() RETURNS void AS $$
+ try:
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  If the second literalUPDATE/literal statement results in an exception
+  being raised, this function will report the error, but the result of the
+  first literalUPDATE/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 literalplpy.execute/literal
+  calls in an explicit subtransaction. The literalplpy/literal module
+  provides a helper object to manage explicit subtransactions that gets
+  created with literalplpy.subtransaction()/literal function. Objects created
+  by this function implements the context manager interface as defined by
+  ulink url=http://www.python.org/dev/peps/pep-0343/;PEP 343/ulink.
+  Using explicit subtransacrions we can rewrite our function as:
+ programlisting
+ CREATE FUNCTION transfer_funds2() RETURNS void AS $$
+ try:
+ with plpy.subtransaction():
+ plpy.execute(UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe')
+ plpy.execute(UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary')
+ except plpy.SPIError, e:
+ result = error transferring funds: %s % e.args
+ else:
+ result = funds transferred correctly
+ plpy.execute(INSERT INTO operations(result) VALUES ('%s') % result)
+ $$ LANGUAGE plpythonu;
+ /programlisting
+  Note that the use of literaltry/catch/literal is still
+  required. Otherwise the exception would propagate to the 

Re: [HACKERS] pl/python explicit subtransactions

2011-02-02 Thread Steve Singer

On 11-01-27 05:11 PM, Jan Urbański wrote:

On 23/12/10 15:32, Jan Urbański wrote:

Here's a patch implementing explicitly starting subtransactions mentioned in
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
an incremental patch on top of the spi-in-subxacts patch sent eariler.


Updated to the spi-in-subxacts version sent earlier.






Submission Review
-

The patch applies against master.
Test updates are included.

The patch doesn't included any documentation updates.  The author did 
mention that he'll do these if it looks like the patch is going to be 
accepted.


The plpython_subxact regression test you addded is failing on both 
python3 and 2.4 for me.  It seems to be creating functions with the same 
name twice and the second time is failing with ERROR: function . 
already exists.  I think this is just an issue with your expect files.


Usability Review
---

The patch implements a python context manager that allows plpython 
programs to control subtransactions with the python 'with' syntax.
The patch implements what it describes.  Using the subtransaction 
manager seems consistent with other examples of Python context managers. 
 This feature seems useful for pl/python developers.


The 'with' syntax was only officially added with python 2.6.  I have 
confirmed that the patch does not break plpython going as far back as 
2.5 and 2.4.  I have no reason to think that earlier versions will be 
broken either, I just didn't test anything earlier than 2.4.


I think this feature is useful for developing more complicated functions 
in pl/python and we don't have an existing way of managing savepoints 
from pl/python.  The context manager approach seems consistent with how 
recent python versions deal with this type of thing in other areas.




Feature Test

No issues found.


Code Review



PLy_abort_open_subtransactions(...) line 1215:

ereport(WARNING,
(errmsg(Forcibly aborting a subtransaction 
that has not been exited)));

Forcibly should be forcibly (lower case)

Similarly in PLy_subxact_enter and PLy_subxact_exit a few
PLy_exception_set calls start with an upper case character when I think 
we want it to be lower case.



Other than that I don't see any issues.  I am marking this as waiting 
for author since the documentation is still outstanding.







--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-02-02 Thread Peter Eisentraut
On tor, 2010-12-23 at 15:32 +0100, Jan Urbański wrote:
 with plpy.subxact():
 plpy.execute(insert into t values (1))
 plpy.execute(insert into t values (2))
 plpy.execute(ooops)

Looks pretty cool, but maybe s/subxact/subtransaction/.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] pl/python explicit subtransactions

2011-01-27 Thread Jan Urbański
On 23/12/10 15:32, Jan Urbański wrote:
 Here's a patch implementing explicitly starting subtransactions mentioned in
 http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
 an incremental patch on top of the spi-in-subxacts patch sent eariler.

Updated to the spi-in-subxacts version sent earlier.
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 16d78ae..33dddc6 100644
*** a/src/pl/plpython/Makefile
--- b/src/pl/plpython/Makefile
*** REGRESS = \
*** 79,84 
--- 79,85 
  	plpython_types \
  	plpython_error \
  	plpython_unicode \
+ 	plpython_subxact \
  	plpython_drop
  # where to find psql for running the tests
  PSQLDIR = $(bindir)
diff --git a/src/pl/plpython/expected/README b/src/pl/plpython/expected/README
index f011528..362cc0d 100644
*** a/src/pl/plpython/expected/README
--- b/src/pl/plpython/expected/README
*** plpython_unicode_2.out		Python 2.2
*** 6,8 
--- 6,11 
  plpython_unicode_3.out		Python 2.3 through 3.1
  
  plpython_types_3.out		Python 3.1
+ 
+ plpython_subxact.out		Python 2.6 through 3.1
+ plpython_subxact_0.out		older Pythons that don't have the with statement
diff --git a/src/pl/plpython/expected/plpython_subxact.out b/src/pl/plpython/expected/plpython_subxact.out
index ...9888a20 .
*** a/src/pl/plpython/expected/plpython_subxact.out
--- b/src/pl/plpython/expected/plpython_subxact.out
***
*** 0 
--- 1,330 
+ -- test explicit subtransaction starting
+ /* Test table to see if transactions get properly rolled back
+  */
+ CREATE TABLE subxact_tbl (
+ i integer
+ );
+ /* Explicit case for Python 2.6
+  */
+ CREATE FUNCTION subxact_test(what_error text = NULL) RETURNS text
+ AS $$
+ import sys
+ subxact = plpy.subxact()
+ subxact.__enter__()
+ exc = True
+ try:
+ try:
+ plpy.execute(insert into subxact_tbl values(1))
+ plpy.execute(insert into subxact_tbl values(2))
+ if what_error == SPI:
+ plpy.execute(insert into subxact_tbl values('oops'))
+ elif what_error == Python:
+ plpy.attribute_error
+ except:
+ exc = False
+ subxact.__exit__(*sys.exc_info())
+ raise
+ finally:
+ if exc:
+ subxact.__exit__(None, None, None)
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_test();
+  subxact_test 
+ --
+  
+ (1 row)
+ 
+ SELECT * FROM subxact_tbl;
+  i 
+ ---
+  1
+  2
+ (2 rows)
+ 
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('SPI');
+ ERROR:  plpy.SPIError: invalid input syntax for integer: oops
+ LINE 1: insert into subxact_tbl values('oops')
+^
+ QUERY:  insert into subxact_tbl values('oops')
+ CONTEXT:  PL/Python function subxact_test
+ SELECT * FROM subxact_tbl;
+  i 
+ ---
+ (0 rows)
+ 
+ TRUNCATE subxact_tbl;
+ SELECT subxact_test('Python');
+ ERROR:  AttributeError: 'module' object has no attribute 'attribute_error'
+ CONTEXT:  PL/Python function subxact_test
+ SELECT * FROM subxact_tbl;
+  i 
+ ---
+ (0 rows)
+ 
+ TRUNCATE subxact_tbl;
+ /* Context manager case for Python =2.6
+  */
+ CREATE FUNCTION subxact_ctx_test(what_error text = NULL) RETURNS text
+ AS $$
+ with plpy.subxact():
+ plpy.execute(insert into subxact_tbl values(1))
+ plpy.execute(insert into subxact_tbl values(2))
+ if what_error == SPI:
+ plpy.execute(insert into subxact_tbl values('oops'))
+ elif what_error == Python:
+ plpy.attribute_error
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_ctx_test();
+  subxact_ctx_test 
+ --
+  
+ (1 row)
+ 
+ SELECT * FROM subxact_tbl;
+  i 
+ ---
+  1
+  2
+ (2 rows)
+ 
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('SPI');
+ ERROR:  plpy.SPIError: invalid input syntax for integer: oops
+ LINE 1: insert into subxact_tbl values('oops')
+^
+ QUERY:  insert into subxact_tbl values('oops')
+ CONTEXT:  PL/Python function subxact_ctx_test
+ SELECT * FROM subxact_tbl;
+  i 
+ ---
+ (0 rows)
+ 
+ TRUNCATE subxact_tbl;
+ SELECT subxact_ctx_test('Python');
+ ERROR:  AttributeError: 'module' object has no attribute 'attribute_error'
+ CONTEXT:  PL/Python function subxact_ctx_test
+ SELECT * FROM subxact_tbl;
+  i 
+ ---
+ (0 rows)
+ 
+ TRUNCATE subxact_tbl;
+ /* Nested subtransactions
+  */
+ CREATE FUNCTION subxact_nested_test(swallow boolean = 'f') RETURNS text
+ AS $$
+ plpy.execute(insert into subxact_tbl values(1))
+ with plpy.subxact():
+ plpy.execute(insert into subxact_tbl values(2))
+ try:
+ with plpy.subxact():
+ plpy.execute(insert into subxact_tbl values(3))
+ plpy.execute(error)
+ except plpy.SPIError, e:
+ if not swallow:
+ raise
+ plpy.notice(Swallowed %r % e)
+ return ok
+ $$ LANGUAGE plpythonu;
+ SELECT subxact_nested_test();
+ ERROR:  plpy.SPIError: syntax error at or near error
+ LINE 1: error
+ ^
+ QUERY:  error
+ CONTEXT:  PL/Python function subxact_nested_test
+