Re: JDBC feature request: auto savepoint per command

[Mark Lewis <mark(dot)lewis(at)mir3(dot)com>]

The idea has been raised on this list before:
http://archives.postgresql.org/pgsql-performance/2006-07/msg00245.php

There is a negative performance impact but there's no reason it couldn't
be a driver option.  There isn't one right now though.

-- Mark Lewis

On Mon, 2007-04-23 at 14:25 -0400, Tom Lane wrote:
> Some folk at JBoss (now part of Red Hat) are complaining to me that
> there's no way to deal with failing statements within a transaction
> in the context of CMT (Container Managed Transactions).  The normal
> advice would be to do SAVEPOINT and RELEASE around each statement,
> but they say this is disallowed:
> 
> > It's really a question of granularity. When using CMT a global  
> > transaction is not committed until the end of method completion.  
> > Prior to the JDBC connection being used, autocommit is set to false  
> > and the global transaction is officially started. When the global  
> > transaction completes, the transaction is committed as a whole. JDBC  
> > Rollback and commit within a managed transaction is prohibited as  
> > this is the responsibility of the container. So, setting a savepoint  
> > and rolling back (and subsequently committing) won't work in this  
> > context which is what the Postgres driver requires.
> 
> Can anyone see a solution for this in the context of the existing driver
> facilities?  If not, is it reasonable to ask for a driver option to do
> auto-savepointing?  There is already an equivalent facility in psql, so
> the idea doesn't seem unreasonable to me.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend