Re: Two features left

I totally agree.

As I understand it, every function being executed is either inside an
explicit or implicit transaction. What I would like is the ability to
commit/rollback within a function (that would also implicitly start another
transaction).

This would have the following benefits:
1. If a large amount of processing is going on before an exception occurs,
only the stuff since the last commit will be lost.
2. It reduces load on your rollback segments (sorry, oracle speak here).
3. It paves the way for exception handling within your code.

Exception handling is probably the biggest thing that I need from a
programming language that plpgsql does not have. With exception handling,
you can rollback the error, but log (and commit) information about the error
to an error log table. This is enormously helpful for troubleshooting
purposes when such errors occur. Exceptions are also awesome because it
enables you to write code that fails gracefully rather than having to
anticipate and code for every possible thing that can go wrong.

Cheers,
Mark

----- Original Message -----
From: "Jean-Luc Lachance" <jllachan(at)nsd(dot)ca>
To: "Jon Swinth" <jswinth(at)atomicpc(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, November 29, 2002 4:59 AM
Subject: Re: [GENERAL] Two features left

> I keep reading discussion about the ablility to ABORT part of a
> transaction, but what I was really looking for is the ability to COMMIT
> part of a transaction.
>
> Actually the word transaction is missleading. By definition, a
> transaction should be atomic ie commit all or non.
>
> My beef is that a transaction is automatically started once you call a
> function.
>
> As someone mentioned earlier, we (I) need cursor out of transaction.
> Better yet, I need the ability to start and end a transaction anywhere
> in my code.
>
> JLL
>
>
> Jon Swinth wrote:
> >
> > > From: Timur Irmatov <itvthor(at)sdf(dot)lonestar(dot)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.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>