Обсуждение: Re: [GENERAL] Transactions within a function body
On Thu, Oct 2, 2008 at 8:40 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
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,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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 selectivelyNo -- I mean you can use BEGIN/EXCEPTION/END blocks as you like, nesting
> > 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?
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,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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.
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."
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 <alvherre@commandprompt.com> wrote:
Gurjeet Singh escribió:Initially we aimed at just exposing SAVEPOINT and ROLLBACK TO in
> 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?
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.
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
On Thu, Oct 2, 2008 at 6:46 PM, Bob Henkel <bob.henkel@gmail.com> wrote:
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=647The 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).
The same can be done with plProxy which is quite efficient but yes opening connections is not. So if used extensively it would be clever to use pgBouncer to reuse connections. Thanks for interesting idea.
Bob
"Hi all.Thanks."
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.On Thu, Oct 2, 2008 at 10:40 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:Gurjeet Singh escribió:Initially we aimed at just exposing SAVEPOINT and ROLLBACK TO in
> 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?
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.
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)http://www.postgresql.org/mailpref/pgsql-general
To make changes to your subscription: