Thread: Savepoint performance
All, I support a system that runs on several databases including PostgreSQL. I've noticed that the other DB's always put an implicit savepoint before each statement executed, and roll back to that savepoint if the statement fails for some reason. PG does not, so unless you manually specify a savepoint you lose all previous work in the transaction. 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. My main reason for wanting this is so that I can more easily import, say, 50 new 'objects' (related rows stored across several tables) in a transaction instead of only one at a time without fear that an error in one object would invalidate the whole batch. I could do this now by manually setting savepoints, but if it's not a big deal performance-wise to modify the JDBC driver to start an anonymous savepoint with each statement, then I'd prefer that approach as it seems that it would make life easier for other folks too. Thanks in advance for any feedback :) -- Mark Lewis
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
On 7/27/06, Mark Lewis <mark.lewis@mir3.com> wrote:
> All,
>
> I support a system that runs on several databases including PostgreSQL.
> I've noticed that the other DB's always put an implicit savepoint before
> each statement executed, and roll back to that savepoint if the
> statement fails for some reason. PG does not, so unless you manually
> specify a savepoint you lose all previous work in the transaction.
>
you're talking about transactions not savepoints (savepoints is
something more like nested transactions), i guess...
postgres execute every single statement inside an implicit transaction
unless you put BEGIN/COMMIT between a block of statements... in that
case if an error occurs the entire block of statements must
ROLLBACK...
if other db's doesn't do that, is a bug in their implementation of the
SQL standard
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
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
"Denis Lussier" <denisl@enterprisedb.com> 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
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 <tgl@sss.pgh.pa.us> wrote: > "Denis Lussier" <denisl@enterprisedb.com> 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 >
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/