We've actually done some prelim benchmarking of this feature about six months ago and we are actively considering adding it to our "closer to Oracle" version of PLpgSQL. I certainly don't want to suggest that it's a good idea to do this because it's Oracle compatible. :-)
I'll get someone to post our performance results on this thread. As Alvaro correctly alludes, it has an overhead impact that is measurable, but, likely acceptable for situations where the feature is desired (as long as it doesn't negatively affect performance in the "normal" case). I believe the impact was something around a 12% average slowdown for the handful of PLpgSQL functions we tested when this feature is turned on.
Would the community be potentially interested in this feature if we created a BSD Postgres patch of this feature for PLpgSQL (likely for 8.3)??
--Luss
Denis Lussier
CTO
On 7/27/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:Mark Lewis wrote:
> 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.
It is moderately expensive. It's cheaper than starting/committing a
transaction, but certainly much more expensive than not setting a
savepoint.
In psql you can do what you want using \set ON_ERROR_ROLLBACK on. This
is clearly a client-only issue, so the server does not provide any
special support for it (just like autocommit mode).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings