Re: [GENERAL] Transactions within a function body

[Gurjeet Singh]

No, in Oracle too SAVEPOINT and AUTONOMOUS transaction are different beasts.

On Thu, Oct 2, 2008 at 9:27 PM, Bob Henkel <[EMAIL PROTECTED]> wrote:

> Coming from an Oracle background my understanding is they're one in the
> same.
>
>
>
>
> On Thu, Oct 2, 2008 at 10:37 AM, Alvaro Herrera <
> [EMAIL PROTECTED]> wrote:
>
>> Bob Henkel escribió:
>> > Have you looked at creating a function in perl and creating a new
>> > connection? Or using a dblink query which can create a new connection?
>> > These two methods work. I have used them to insert to a log table
>> regardless
>> > of the parent transaction being commited or rolled back.
>>
>> That's a different thing, "autonomous transactions".
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/ 
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
>
>


-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] Transactions within a function body

[Bob Henkel]

Coming from an Oracle background my understanding is they're one in the
same.




On Thu, Oct 2, 2008 at 10:37 AM, Alvaro Herrera
<[EMAIL PROTECTED]>wrote:

> Bob Henkel escribió:
> > Have you looked at creating a function in perl and creating a new
> > connection? Or using a dblink query which can create a new connection?
> > These two methods work. I have used them to insert to a log table
> regardless
> > of the parent transaction being commited or rolled back.
>
> That's a different thing, "autonomous transactions".
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ 
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Re: [GENERAL] Transactions within a function body

[Bob Henkel]

Have you looked at creating a function in perl and creating a new
connection? Or using a dblink query which can create a new connection?
These two methods work. I have used them to insert to a log table regardless
of the parent transaction being commited or rolled back.

A old example I posted of using pl/perl can be found here ->
http://www.postgresqlforums.com/forums/viewtopic.php?f=4&t=647

The key is opening a new session which using dblink or pl/perl dbi
connection will do. This is not ideal or efficient.  It would be nice if you
could just do autonomous transactions natively in pl/pgsql, but I find this
method works for the cases where you need it(logging, huge batch processing
tasks where it's not ideal to process everything in one transaction).

Bob

"Hi all.
Is there a way to have (sub)transactions within a function body?
I'd like to execute some code (a transaction!) inside a function and later
decide whether that transaction is to be committed or not.
Thanks."

On Thu, Oct 2, 2008 at 10:40 AM, Alvaro Herrera
<[EMAIL PROTECTED]>wrote:

> Gurjeet Singh escribió:
>
> > I have seen this feature being asked for, and this work-around suggested
> so
> > many times. If plpgql does it internally, why not provide a clean
> interface
> > for this? Is there some road-block, or that nobody has ever tried it?
>
> Initially we aimed at just exposing SAVEPOINT and ROLLBACK TO in
> functions, but ran into the problem that the SPI stack needs to be dealt
> with appropriately and you can't do it if the user is able to modify it
> arbitrarily by calling transaction-modifying commands.  That's when the
> EXCEPTION idea came up.  We never went back and studied whether we could
> have fixed the SPI limitation, but it's not trivial.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ 
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Transactions within a function body

[Alvaro Herrera]

Gurjeet Singh escribió:

> I have seen this feature being asked for, and this work-around suggested so
> many times. If plpgql does it internally, why not provide a clean interface
> for this? Is there some road-block, or that nobody has ever tried it?

Initially we aimed at just exposing SAVEPOINT and ROLLBACK TO in
functions, but ran into the problem that the SPI stack needs to be dealt
with appropriately and you can't do it if the user is able to modify it
arbitrarily by calling transaction-modifying commands.  That's when the
EXCEPTION idea came up.  We never went back and studied whether we could
have fixed the SPI limitation, but it's not trivial.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Alvaro Herrera]

Bob Henkel escribió:
> Have you looked at creating a function in perl and creating a new
> connection? Or using a dblink query which can create a new connection?
> These two methods work. I have used them to insert to a log table regardless
> of the parent transaction being commited or rolled back.

That's a different thing, "autonomous transactions".

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Gurjeet Singh]

On Thu, Oct 2, 2008 at 8:40 PM, Alvaro Herrera
<[EMAIL PROTECTED]>wrote:

> Reg Me Please escribió:
> > Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto:
>
> > > You can nest blocks arbitrarily, giving you the chance to selectively
> > > rollback pieces of the function.  It's only a bit more awkward.
> >
> > You mean I can issue a ROLLBACK command within a BEGIN...END; block to
> roll it
> > back?
>
> No -- I mean you can use BEGIN/EXCEPTION/END blocks as you like, nesting
> them or putting one after another.  Complementing this with RAISE
> EXCEPTION you can cause savepoints to roll back at will.


I have seen this feature being asked for, and this work-around suggested so
many times. If plpgql does it internally, why not provide a clean interface
for this? Is there some road-block, or that nobody has ever tried it?

If there are no known limitations, I'd like to start work on it.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] Transactions within a function body

[Bob Henkel]

Have you looked at creating a function in perl and creating a new
connection? Or using a dblink query which can create a new connection?
These two methods work. I have used them to insert to a log table regardless
of the parent transaction being commited or rolled back.

A old example I posted of using pl/perl can be found here ->
http://www.postgresqlforums.com/forums/viewtopic.php?f=4&t=647

The key is opening a new session which using dblink or pl/perl dbi
connection will do. This is not ideal as it would be nice if you could just
do autonomous transactions, but I find this method works for the cases where
you need it.

Bob

"Hi all.

Is there a way to have (sub)transactions within a function body?
I'd like to execute some code (a transaction!) inside a function and later
decide whether that transaction is to be committed or not.

Thanks."

Re: [GENERAL] Transactions within a function body

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Reg Me Please escribió:
>> You mean I can issue a ROLLBACK command within a BEGIN...END; block to roll 
>> it 
>> back?

> No -- I mean you can use BEGIN/EXCEPTION/END blocks as you like, nesting
> them or putting one after another.  Complementing this with RAISE
> EXCEPTION you can cause savepoints to roll back at will.

Yeah, it's essentially the same functionality as savepoints, but
different syntax.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Reg Me Please]

Il Thursday 02 October 2008 17:10:23 Alvaro Herrera ha scritto:
> Reg Me Please escribió:
> > Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto:
> > > You can nest blocks arbitrarily, giving you the chance to selectively
> > > rollback pieces of the function.  It's only a bit more awkward.
> >
> > You mean I can issue a ROLLBACK command within a BEGIN...END; block to
> > roll it back?
>
> No -- I mean you can use BEGIN/EXCEPTION/END blocks as you like, nesting
> them or putting one after another.  Complementing this with RAISE
> EXCEPTION you can cause savepoints to roll back at will.

Now I understand. (Sorry, me dumb!)

Looks quirky, but I trust it's working. I'll give that a try.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Alvaro Herrera]

Reg Me Please escribió:
> Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto:

> > You can nest blocks arbitrarily, giving you the chance to selectively
> > rollback pieces of the function.  It's only a bit more awkward.
> 
> You mean I can issue a ROLLBACK command within a BEGIN...END; block to roll 
> it 
> back?

No -- I mean you can use BEGIN/EXCEPTION/END blocks as you like, nesting
them or putting one after another.  Complementing this with RAISE
EXCEPTION you can cause savepoints to roll back at will.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Reg Me Please]

Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto:
> Reg Me Please escribió:
> > Well, if it is a limitation, and having it would lead to a "better
> > product", why not making it a feature for the next still-open release?
>
> Because no one is working on implementing it?
>
> > In my opinion that's more than a limitation, it's a missing feature.
> > In your code you often need to create savepoints to delay the decision
> > for the commitment.
> > A Pl/PgSQL function is just a bunch of code you want to move into the DB.
> > So the need for savepoints seems to me to be still there.
>
> You can nest blocks arbitrarily, giving you the chance to selectively
> rollback pieces of the function.  It's only a bit more awkward.

You mean I can issue a ROLLBACK command within a BEGIN...END; block to roll it 
back?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Alvaro Herrera]

Reg Me Please escribió:
> Well, if it is a limitation, and having it would lead to a "better product",
> why not making it a feature for the next still-open release?

Because no one is working on implementing it?

> In my opinion that's more than a limitation, it's a missing feature.
> In your code you often need to create savepoints to delay the decision for 
> the 
> commitment.
> A Pl/PgSQL function is just a bunch of code you want to move into the DB.
> So the need for savepoints seems to me to be still there.

You can nest blocks arbitrarily, giving you the chance to selectively
rollback pieces of the function.  It's only a bit more awkward.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Albe Laurenz]

Richard Huxton wrote:
>> After a discussion on comp.databases.postgresql I realized that this
>> is actually a limitation.
>> 
>> Consider the following:
>> 
>> BEGIN
>>UPDATE ...
>>UPDATE ...
>>UPDATE ...
>> EXCEPTION
>>WHEN integrity_constraint_violation THEN
>>   ...
>> END;
>> 
>> If the first UPDATE succeeds but the second one bombs, there is no way
>> to undo the first update short of having the whole transaction cancelled.
> 
> No, I think you've got that backwards Albe. You can even nest exceptions.
> 
[...]
> 
> The BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".

You are right, and I'm happy to find myself wrong:

CREATE TABLE t1 (a integer PRIMARY KEY);

CREATE FUNCTION test_exception() RETURNS boolean LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (2);
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (3);
   RETURN TRUE;
EXCEPTION
   WHEN integrity_constraint_violation THEN
  RAISE NOTICE 'Rollback to savepoint';
  RETURN FALSE;
END;$$;

BEGIN;

SELECT test_exception();
NOTICE:  Rollback to savepoint
 test_exception 

 f
(1 row)

COMMIT;

SELECT count(*) FROM t1;
 count 
---
 0
(1 row)

Great, thank you!

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Reg Me Please]

Hi.

My humble opinion follows.

One point here is that the decision for the ROLLBACK could possibly be 
different from errors.
It could simply be based upon a generic expression, not just the conditions 
seen in "Appendix A" of the manual.
An exception is something different from a transaction, despite the former is 
implemented with the latter.


On Thursday 02 October 2008 11:53:17 Richard Huxton wrote:
> Albe Laurenz wrote:
> > After a discussion on comp.databases.postgresql I realized that this
> > is actually a limitation.
> >
> > Consider the following:
> >
> > BEGIN
> >UPDATE ...
> >UPDATE ...
> >UPDATE ...
> > EXCEPTION
> >WHEN integrity_constraint_violation THEN
> >   ...
> > END;
> >
> > If the first UPDATE succeeds but the second one bombs, there is no way
> > to undo the first update short of having the whole transaction cancelled.
>
> No, I think you've got that backwards Albe. You can even nest exceptions.
>
> > If you need all three of these UPDATEs to either all succeed or fail,
> > but the whole transaction should continue, you cannot do that in
> > PL/pgSQL.
>
> Try the following script. By commenting out the second INSERT you can
> change whether you get one or no rows inserted into t1. The
> BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".
>
>
> BEGIN;
>
> CREATE TABLE t1 (a integer);
>
> CREATE OR REPLACE FUNCTION test_exception()
> RETURNS boolean AS $$
> DECLARE
> n integer;
> BEGIN
> INSERT INTO t1 (a) VALUES (1);
> -- INSERT INTO t1 (a) VALUES ('b');
> BEGIN
> INSERT INTO t1 (a) VALUES (2);
> INSERT INTO t1 (a) VALUES ('c');
> EXCEPTION
> WHEN OTHERS THEN
> SELECT INTO n count(*) FROM t1;
> RAISE NOTICE 'n2 = %', n;
> RETURN false;
> END;
> RETURN true;
> EXCEPTION
> WHEN OTHERS THEN
> SELECT INTO n count(*) FROM t1;
> RAISE NOTICE 'n1 = %', n;
> RETURN false;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT test_exception();
>
> SELECT count(*) FROM t1;
>
> ROLLBACK;



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Reg Me Please]

Well, if it is a limitation, and having it would lead to a "better product",
why not making it a feature for the next still-open release?

In my opinion that's more than a limitation, it's a missing feature.
In your code you often need to create savepoints to delay the decision for the 
commitment.
A Pl/PgSQL function is just a bunch of code you want to move into the DB.
So the need for savepoints seems to me to be still there.

Useless to say I would vote for a "GO".

On Thursday 02 October 2008 11:01:37 Albe Laurenz wrote:
> Alvaro Herrera wrote:
> > > > Is there a way to have (sub)transactions within a function body?
> > > > I'd like to execute some code (a transaction!) inside a function and
> > > > later decide whether that transaction is to be committed or not.
> > >
> > > You could issue a "SAVEPOINT name". If at the end you don't want your
> > > changes to apply, you can issue a "ROLLBACK to name"
> >
> > Actually you can't use SAVEPOINT nor ROLLBACK TO within a function.  In
> > PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes,
> > just do a RAISE EXCEPTION, and the exception block is run).
>
> After a discussion on comp.databases.postgresql I realized that this
> is actually a limitation.
>
> Consider the following:
>
> BEGIN
>UPDATE ...
>UPDATE ...
>UPDATE ...
> EXCEPTION
>WHEN integrity_constraint_violation THEN
>   ...
> END;
>
> If the first UPDATE succeeds but the second one bombs, there is no way
> to undo the first update short of having the whole transaction cancelled.
>
> So while exceptions are implemented using savepoints, they give you only
> part of the functionality, namely to make a group of statements
> all-or-nothing within one transaction.
>
> If you need all three of these UPDATEs to either all succeed or fail,
> but the whole transaction should continue, you cannot do that in PL/pgSQL.
>
> Is there a chance to get savepoint support in PL/pgSQL at some point?
> Does it make sense to raise this on -hackers?
>
> Yours,
> Laurenz Albe



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Richard Huxton]

Albe Laurenz wrote:
> After a discussion on comp.databases.postgresql I realized that this
> is actually a limitation.
> 
> Consider the following:
> 
> BEGIN
>UPDATE ...
>UPDATE ...
>UPDATE ...
> EXCEPTION
>WHEN integrity_constraint_violation THEN
>   ...
> END;
> 
> If the first UPDATE succeeds but the second one bombs, there is no way
> to undo the first update short of having the whole transaction cancelled.

No, I think you've got that backwards Albe. You can even nest exceptions.

> If you need all three of these UPDATEs to either all succeed or fail,
> but the whole transaction should continue, you cannot do that in PL/pgSQL.

Try the following script. By commenting out the second INSERT you can
change whether you get one or no rows inserted into t1. The
BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".


BEGIN;

CREATE TABLE t1 (a integer);

CREATE OR REPLACE FUNCTION test_exception()
RETURNS boolean AS $$
DECLARE
n integer;
BEGIN
INSERT INTO t1 (a) VALUES (1);
-- INSERT INTO t1 (a) VALUES ('b');
BEGIN
INSERT INTO t1 (a) VALUES (2);
INSERT INTO t1 (a) VALUES ('c');
EXCEPTION
WHEN OTHERS THEN
SELECT INTO n count(*) FROM t1;
RAISE NOTICE 'n2 = %', n;
RETURN false;
END;
RETURN true;
EXCEPTION
WHEN OTHERS THEN
SELECT INTO n count(*) FROM t1;
RAISE NOTICE 'n1 = %', n;
RETURN false;
END;
$$ LANGUAGE plpgsql;

SELECT test_exception();

SELECT count(*) FROM t1;

ROLLBACK;

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Albe Laurenz]

Alvaro Herrera wrote:
> > > Is there a way to have (sub)transactions within a function body?
> > > I'd like to execute some code (a transaction!) inside a function and later
> > > decide whether that transaction is to be committed or not.
> > 
> > You could issue a "SAVEPOINT name". If at the end you don't want your
> > changes to apply, you can issue a "ROLLBACK to name"
> 
> Actually you can't use SAVEPOINT nor ROLLBACK TO within a function.  In
> PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes,
> just do a RAISE EXCEPTION, and the exception block is run).

After a discussion on comp.databases.postgresql I realized that this
is actually a limitation.

Consider the following:

BEGIN
   UPDATE ...
   UPDATE ...
   UPDATE ...
EXCEPTION
   WHEN integrity_constraint_violation THEN
  ...
END;

If the first UPDATE succeeds but the second one bombs, there is no way
to undo the first update short of having the whole transaction cancelled.

So while exceptions are implemented using savepoints, they give you only
part of the functionality, namely to make a group of statements
all-or-nothing within one transaction.

If you need all three of these UPDATEs to either all succeed or fail,
but the whole transaction should continue, you cannot do that in PL/pgSQL.

Is there a chance to get savepoint support in PL/pgSQL at some point?
Does it make sense to raise this on -hackers?

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Alvaro Herrera]

Dennis Brakhane escribió:
> On Wed, Oct 1, 2008 at 5:54 PM, Reg Me Please <[EMAIL PROTECTED]> wrote:
> > Hi all.
> >
> > Is there a way to have (sub)transactions within a function body?
> > I'd like to execute some code (a transaction!) inside a function and later
> > decide whether that transaction is to be committed or not.
> 
> You could issue a "SAVEPOINT name". If at the end you don't want your
> changes to apply, you can issue a "ROLLBACK to name"

Actually you can't use SAVEPOINT nor ROLLBACK TO within a function.  In
PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes,
just do a RAISE EXCEPTION, and the exception block is run).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Dennis Brakhane]

On Wed, Oct 1, 2008 at 5:54 PM, Reg Me Please <[EMAIL PROTECTED]> wrote:
> Hi all.
>
> Is there a way to have (sub)transactions within a function body?
> I'd like to execute some code (a transaction!) inside a function and later
> decide whether that transaction is to be committed or not.

You could issue a "SAVEPOINT name". If at the end you don't want your
changes to apply, you can issue a "ROLLBACK to name"

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Transactions within a function body

[Andreas Kretschmer]

Reg Me Please <[EMAIL PROTECTED]> schrieb:

> Hi all.
> 
> Is there a way to have (sub)transactions within a function body?

No.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general