Subject: Savepoint performance
From: Mark Lewis <mark.lewis@mir3.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Thu, 27 Jul 2006 10:19:15 -0700
Message-Id: <1154020755.1634.509.camel@archimedes>
Mime-Version: 1.0

All,

I support a system that runs on several databases including PostgreSQL.
I've noticed that the other DB's always put an implicit savepoint before
each statement executed, and roll back to that savepoint if the
statement fails for some reason.  PG does not, so unless you manually
specify a savepoint you lose all previous work in the transaction.

So my question is, how expensive is setting a savepoint in PG?  If it's
not too expensive, I'm wondering if it would be feasible to add a config
parameter to psql or other client interfaces (thinking specifically of
jdbc here) to do it automatically.  Doing so would make it a little
easier to work with PG in a multi-db environment.

My main reason for wanting this is so that I can more easily import,
say, 50 new 'objects' (related rows stored across several tables) in a
transaction instead of only one at a time without fear that an error in
one object would invalidate the whole batch.  I could do this now by
manually setting savepoints, but if it's not a big deal performance-wise
to modify the JDBC driver to start an anonymous savepoint with each
statement, then I'd prefer that approach as it seems that it would make
life easier for other folks too.

Thanks in advance for any feedback :)

-- Mark Lewis