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@xxxxxxxxxxxxxx > > >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@xxxxxxxxxxxxxxxx >Iselin, New Jersey 08830 | http://www.enterprisedb.com/
