Thread: Problem using start transaction in nested transactions.
Hi All,
I have a small problem in using nested transactions while working on Postgres 8.0.
Ex: I have a function A() which in turn calls functions b() and c() , if i want commit something in b or c. i have to use
start transaction read write;
-- set of sql statements and then say commit.
commit;
but this is not working it is showing the the following error near start transaction read write
" ERROR: SPI_execute_plan failed executing query "start transaction read write": SPI_ERROR_TRANSACTION ";
or
if i use only commit with out the start transaction command it throwing the runtime error
as
" ERROR: SPI_execute_plan failed executing query "commit": SPI_ERROR_TRANSACTION ";
Can any one send me an example of how to use the start transaction or how to commit an nested transaction.
Thanks in advance.
Thanks & Regards,
Sri
You can't do subtransactions in PostgreSQL, but you can use savepoints: http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html. Previously, you couldn't do any transactional stuff from inside functions. I'm not sure if you can do savepoint-related stuff from inside functions in 8.0 or not. On Mon, Oct 17, 2005 at 12:40:01PM +0530, Sri wrote: > Hi All, > > I have a small problem in using nested transactions while working on Postgres 8.0. > > Ex: I have a function A() which in turn calls functions b() and c() , if i want commit something in b or c. i have to use > > start transaction read write; > -- set of sql statements and then say commit. > commit; > > but this is not working it is showing the the following error near start transaction read write > " ERROR: SPI_execute_plan failed executing query "start transaction read write": SPI_ERROR_TRANSACTION "; > > or > > if i use only commit with out the start transaction command it throwing the runtime error > as > > > " ERROR: SPI_execute_plan failed executing query "commit": SPI_ERROR_TRANSACTION "; > > > Can any one send me an example of how to use the start transaction or how to commit an nested transaction. > > Thanks in advance. > > > Thanks & Regards, > Sri > > > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes: > You can't do subtransactions in PostgreSQL, but you can use savepoints: > http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html. > > Previously, you couldn't do any transactional stuff from inside > functions. I'm not sure if you can do savepoint-related stuff from > inside functions in 8.0 or not. Yes, you certainly can; PL/pgSQL uses savepoints to do its exception handling. -Doug
Hi, Thanks for the reply , I got it working using another begin and end with in a function. getting in to detail i have done something like this. create function functionName() return void as ' declare : : begin : : begin -- perform some transactions say creation of a sequence or tables or any thing end; : : end; It seems that all the statements between begin and end are treated as one transaction while execution. Thanks & Regards, Sri ----- Original Message ----- From: "Jim C. Nasby" <jnasby@pervasive.com> To: "Sri" <asrinivas@effigent.net> Cc: <pgsql-general@postgresql.org> Sent: Monday, October 17, 2005 11:38 PM Subject: Re: [GENERAL] Problem using start transaction in nested transactions. > You can't do subtransactions in PostgreSQL, but you can use savepoints: > http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html. > > Previously, you couldn't do any transactional stuff from inside > functions. I'm not sure if you can do savepoint-related stuff from > inside functions in 8.0 or not. > > On Mon, Oct 17, 2005 at 12:40:01PM +0530, Sri wrote: > > Hi All, > > > > I have a small problem in using nested transactions while working on Postgres 8.0. > > > > Ex: I have a function A() which in turn calls functions b() and c() , if i want commit something in b or c. i have to use > > > > start transaction read write; > > -- set of sql statements and then say commit. > > commit; > > > > but this is not working it is showing the the following error near start transaction read write > > " ERROR: SPI_execute_plan failed executing query "start transaction read write": SPI_ERROR_TRANSACTION "; > > > > or > > > > if i use only commit with out the start transaction command it throwing the runtime error > > as > > > > > > " ERROR: SPI_execute_plan failed executing query "commit": SPI_ERROR_TRANSACTION "; > > > > > > Can any one send me an example of how to use the start transaction or how to commit an nested transaction. > > > > Thanks in advance. > > > > > > Thanks & Regards, > > Sri > > > > > > > > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 >