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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 <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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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/


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match