X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1C8009FB215 for ; Thu, 27 Jul 2006 22:13:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 76971-03 for ; Thu, 27 Jul 2006 22:13:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.230]) by postgresql.org (Postfix) with ESMTP id ACACD9FACFE for ; Thu, 27 Jul 2006 22:13:18 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 71so243503wra for ; Thu, 27 Jul 2006 18:13:05 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=LZ1iQrLkhxylwKTO+2RaNjnl13PgJu56AfXiUsEEs1zdZAKQuJvkFSlhsc9NjOHIJMxCD+i2+BXaaCmm/rMPdbOVIYgtwNHXhQT++YosW62H7dVh0XZi/RvKRTaeC74Ixmno2QS8GCjpmDoP3xrCkVimrmu/taF2iO6MtfuH65Y= Received: by 10.65.185.13 with SMTP id m13mr481374qbp; Thu, 27 Jul 2006 18:13:05 -0700 (PDT) Received: by 10.64.179.18 with HTTP; Thu, 27 Jul 2006 18:13:05 -0700 (PDT) Message-ID: <5f820f750607271813m454dec68rd0e985deefeaa37e@mail.gmail.com> Date: Thu, 27 Jul 2006 21:13:05 -0400 From: "Denis Lussier" To: "Mark Lewis" , pgsql-performance@postgresql.org Subject: Re: Savepoint performance In-Reply-To: <20060727193520.GG18774@surnet.cl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_118105_32393458.1154049185523" References: <1154020755.1634.509.camel@archimedes> <20060727193520.GG18774@surnet.cl> X-Google-Sender-Auth: 6afe0c9253dfe38f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.535 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200607/252 X-Sequence-Number: 20112 ------=_Part_118105_32393458.1154049185523 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > ------=_Part_118105_32393458.1154049185523 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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

------=_Part_118105_32393458.1154049185523--