Re: Two features left - Mailing list pgsql-general

> From: Timur Irmatov <itvthor@sdf.lonestar.org>
> I am not argueing against nested transactions.
> I'm just trying to say that there should be more natural way of
> allowing transactions to continue other than wrapping each command in
> separate sub-transaction..

Timur, I think what you are failing to grasp is that each SQL call in a
transaction does multiple things: update records, tuples, indexes, lock other
records, etc.  The reason that allowing the transaction to go on is not
simple is because there currently isn't a way to keep track of these things
other than against the transaction itself (from what I gather).  Nested
transactions may help with this only because the mechinisim could be used
internally to mark the beginning of each SQL call.

I have no idea what the difference in difficulty is between nested transaction
and save point.  I will say that nested transaction seems to be complex to
understand and difficult for people to agree on how it should work.
Complexity means that people are not going to use it correctly and/or report
bugs that are not really bugs.  I also think there will be endless debate.

If the nested transaction functionality being invisioned is not really a
seperate transaction within a transaction but rather a marking of a another
begin point and the ability to abort back to that point then this is exactly
the same thing as save point.  This being the case then maybe you shouldn't
call it nested transaction because it doesn't really fit the definition of a
"transaction".  One of the rules of SQL is that two transactions can't both
have a write lock on the same record.  If a child transaction is allowed to
lock and modify the same record that the parent transaction has locked, which
I think you would want, then the child transaction really isn't a
"transaction" is it?

Save point on the other hand is a very clear concept to understand and I think
allows you to accomplish the same thing you are trying to do.  It requires
that changes are tracked in linear order so that you can reverse them to the
save point.  This is not all that simple since a lock issed before and after
the save point on the same record needs to be maintained when rolling back to
the save point.  I don't know how this compares to the way PostgreSQL trackes
changes in a transaction now.

Keep in mind that I am really after the ability to catch a SQL exception and
not void the entire transaction.  Any way we can get that will work for me
provided that the speed of the DB isn't cut in half.  I am just further
offering the opinion that save point may be a better overall solution for
various issues faced.  It is very possible that the Oracle version of save
point came about because the work done to ensure that SQL exceptions did not
kill the previously successful operations in the same transaction.