Savepoint performance

From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Savepoint performance
Date: 2006-07-27 17:19:15
Message-ID: 1154020755.1634.509.camel@archimedes
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Alvaro Herrera 2006-07-27 17:25:48 Re: performance issue with a specific query
Previous Message NbForYou 2006-07-27 16:45:03 Query 200x slower on server [PART 2]