Re: JDBC feature request: auto savepoint per command

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