Re: Savepoint performance

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
http://www.enterprisedb.com

On 7/27/06, Alvaro Herrera <alvherre(at)commandprompt(dot)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
>