Re: [PERFORM] Savepoint performance
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 Herrerahttp://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
Re: [PERFORM] Savepoint performance
On 7/27/06, Mark Lewis [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Savepoint performance
We'veactually done some prelim benchmarking of this feature about six months agoand 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 [EMAIL PROTECTED] 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 atransaction, but certainly much more expensive than not setting asavepoint.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 anyspecial 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
Re: [PERFORM] Savepoint performance
Denis Lussier [EMAIL PROTECTED] 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 6: explain analyze is your friend
Re: [PERFORM] Savepoint performance
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 [EMAIL PROTECTED] wrote: Denis Lussier [EMAIL PROTECTED] 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
