Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
It all depends on what you mean by "nested transaction".
My original assumption was that the original post meant something like
"autonomous transactions" in Oracle, which you cannot have in PostgreSQL.

My suggestion was based on the sample that was posted - I believe that
this can be done with savepoints.

I do not understand your explanation, so I cannot say if this can be
achieved with savepoints or not.

There are of course things that will not be possible in PostgreSQL.
If somebody has a good idea that fits in well with the existing
functionality and is as standard compliant as possible, it might be a
good idea to suggest it on the pgsql-hackers mailing list.
Best is to have an idea how it might work and what benefits will arise
from implementing it.

Yours,
Laurenz Albe

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Laurenz Albe wrote:
Hello Laurenz,

Let's say both stored procedures A and B start with a "begin
transaction" and end with a "commit". This makes them atomic to their
direct users. However, if I where to write stored procedure C that would
like to call both A and B and be atomic itself then I would like to
begin a transaction, call a, call b and then do the commit.
When calling C the only "actual" transaction is the one in C itself. The
ones in A and B would only be savepoints for rollbacks inside A or B,
commits in A and B should be ignored.

Not that I think this is the right way to write stored procedures but I
have had to do some extensions on an existing system where this would
have been very useful. I ended up ripping apart some existing stored
procedures to fix the transaction logic of the new stored procedures
while leaving the existing stuff as untouched as possible.
I had to do a lot of extra testing because of this.

Regards,

Silvio Bierman

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
I see. You are basically looking for savepoints within functions.

But we already have that in the form of exceptions.

Look at the following function:

CREATE OR REPLACE FUNCTION test(i double precision)
RETURNS double precision LANGUAGE plpgsql VOLATILE STRICT AS $$
BEGIN
INSERT INTO log VALUES ('dividing 2 by ' || i);
RETURN 2/i;
EXCEPTION
WHEN OTHERS THEN
RETURN 'NaN';
END;$$;

This should implement your concept of nested transactions:
If an error happens inside the function, all its changes will be rolled
back without affecting a calling function.

It should not be too hard to change existing functions that make use of
nested commits as you describe them to this syntax.

Yours,
Laurenz Albe