Re: Fwd: Savepoint performance - Mailing list pgsql-performance

Actually, what we did in the tests at EnterpriseDB was encapsulate each
SQL statement within its own BEGIN/EXCEPTION/END block.

Using this approach, if a SQL statement aborts, the rollback is
confined 
to the BEGIN/END block that encloses it.  Other SQL statements would
not be affected since the block would isolate and capture that
exception.

In the tests, the base-line version was a PL/pgSQL function for the
dbt-2 new order transaction written within a single BEGIN/END block.
The experimental version was a variation of the base-line altered so
the processing of each order entailed entering three sub-blocks from
the main BEGIN/END block. In addition, another sub-block was
entered each time a detail line within an order was processed.

The transactions per minute were recorded for runs of 20 minutes
simulating 10 terminals and 6 hours simulating 10 terminals.
Below are some of the numbers we got:

                                       With Sub-
                          Test #       Base Line   Blocks       
Difference  % Variation
                          --------      ------------   -----------   
-------------  --------------
10 terminals,           1               6128        5861
20 minutes              2               5700        5702
                           3                6143         5556
                           4               5954          5750
                          5              5695          5925

Average of tests 1 - 5              5924          5758.8        
-165.2         -2.79

10 terminals, 6 hours                5341          5396            
55             1.03

As you can see, we didn't encounter a predictable, significant
difference.

Ernie Nishiseki, Architect
EnterpriseDB Corporation  wrote:

>---------- Forwarded message ----------
>From: Denis Lussier
>Date: Jul 27, 2006 10:33 PM
>Subject: Re: [PERFORM] Savepoint performance
>To: Tom Lane
>Cc: pgsql-performance@postgresql.org
>
>
>My understanding of EDB's approach is that our prototype just
>implicitly does a savepoint before each INSERT, UPDATE, or DELETE
>statement inside of PLpgSQL. We then rollback to that savepoint if a
>sql error occurs. I don 't believe our prelim approach changes any
>transaction start/end semantics on the server side and it doesn't
>change any PLpgSQL syntax either (although it does allow you to
>optionally code commits &/or rollbacks inside stored procs).
>
>Can anybody point me to a thread on the 7.3 disastrous experiment?
>
>I personally think that doing commit or rollbacks inside stored
>procedures is usually bad coding practice AND can be avoided... It's
>a backward compatibility thing for non-ansi legacy stuff and this is
>why I was previously guessing that the community wouldn't be
>interested in this for PLpgSQL. Actually... does anybody know
>offhand if the ansi standard for stored procs allows for explicit
>transaction control inside of a stored procedure?
>
>--Luss
>
>On 7/27/06, Tom Lane wrote:
>>"Denis Lussier" writes:
>>>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)??
>>
>>Based on our rather disastrous experiment in 7.3, I'd say that fooling
>>around with transaction start/end semantics on the server side is
>>unlikely to fly ...
>>
>>regards, tom lane
>>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>--
>Jonah H. Harris, Software Architect | phone: 732.331.1300
>EnterpriseDB Corporation | fax: 732.331.1301
>33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
>Iselin, New Jersey 08830 | http://www.enterprisedb.com/