Thread: Trouble with Savepoints in postgres
Iam not able to use savepoints i postgres.
Iam using version 8.2.
If i write something like this :
CREATE OR REPLACE FUNCTION test_savepoint()
RETURNS void AS
$BODY$
DECLARE
BEGIN
SAVEPOINT foo;
INSERT INTO table1 VALUES (3);
INSERT INTO table1 VALUES (4);
ROLLBACK TO foo;
COMMIT;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
when i try to excute this function it throws me an error:
ERROR: SPI_execute_plan failed executing query "SAVEPOINT foo":
SPI_ERROR_TRANSACTION
SQL state: XX000
Iam not able to understand if this is a version problem or the way iam
using savepoints is wrong.Please advice.
Thanks
Sam
sam escribió: > Iam not able to understand if this is a version problem or the way iam > using savepoints is wrong.Please advice. It is. You cannot use savepoints in PL/pgSQL functions (or any function for that matter). You can use EXCEPTION clauses instead. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Please always ensure that the list is copied on replies (use "Reply to all") so that other people can help you. sam escribió: > On Mar 11, 5:39 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote: > > sam escribió: > > > > > Iam not able to understand if this is a version problem or the way iam > > > using savepoints is wrong.Please advice. > > > > It is. You cannot use savepoints in PL/pgSQL functions (or any function > > for that matter). You can use EXCEPTION clauses instead. > Then u please tell me how save points can be used...........The > program iam working on is throwing an 'LIMIT EXCEEDED' error so iam > trying to commit data so that a total rollback does not occur.Like > commiting data after every 1000 transactions. I figured that > savepoints would be the solution. No, savepoints will not help you there. No matter what you do, you cannot commit in the middle of a function. What's the limit being exceeded? Perhaps you can solve your problem some other way. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)
On Mar 12, 8:11 am, alvhe...@alvh.no-ip.org (Alvaro Herrera) wrote: > Please always ensure that the list is copied on replies (use "Reply to > all") so that other people can help you. > > sam escribió: > > > On Mar 11, 5:39 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote: > > > sam escribió: > > > > > Iam not able to understand if this is a version problem or the way iam > > > > using savepoints is wrong.Please advice. > > > > It is. You cannot use savepoints in PL/pgSQL functions (or any function > > > for that matter). You can use EXCEPTION clauses instead. > > Then u please tell me how save points can be used...........The > > program iam working on is throwing an 'LIMIT EXCEEDED' error so iam > > trying to commit data so that a total rollback does not occur.Like > > commiting data after every 1000 transactions. I figured that > > savepoints would be the solution. > > No, savepoints will not help you there. No matter what you do, you > cannot commit in the middle of a function. > > What's the limit being exceeded? Perhaps you can solve your problem > some other way. > > -- > Alvaro Herrera Developer,http://www.PostgreSQL.org/ > "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio) > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Also can u please tell mewhy i get the error...ERROR: SPI_execute_plan failed executing query "PREPARE TRANSACTION 'foo'": SPI_ERROR_TRANSACTION I get this error when i also use COMMIT, ROLLBACK.....does this mean a patch is missing ? Thanks Sam
On Mar 12, 3:31 pm, sam <sam.mahindra...@gmail.com> wrote: > On Mar 12, 8:11 am, alvhe...@alvh.no-ip.org (Alvaro Herrera) wrote: > > > > > Please always ensure that the list is copied on replies (use "Reply to > > all") so that other people can help you. > > > sam escribió: > > > > On Mar 11, 5:39 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote: > > > > sam escribió: > > > > > > Iam not able to understand if this is a version problem or the way iam > > > > > using savepoints is wrong.Please advice. > > > > > It is. You cannot use savepoints in PL/pgSQL functions (or any function > > > > for that matter). You can use EXCEPTION clauses instead. > > > Then u please tell me how save points can be used...........The > > > program iam working on is throwing an 'LIMIT EXCEEDED' error so iam > > > trying to commit data so that a total rollback does not occur.Like > > > commiting data after every 1000 transactions. I figured that > > > savepoints would be the solution. > > > No, savepoints will not help you there. No matter what you do, you > > cannot commit in the middle of a function. > > > What's the limit being exceeded? Perhaps you can solve your problem > > some other way. > > > -- > > Alvaro Herrera Developer,http://www.PostgreSQL.org/ > > "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio) > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > Also can u please tell mewhy i get the error...ERROR: SPI_execute_plan > failed executing query "PREPARE TRANSACTION 'foo'": > SPI_ERROR_TRANSACTION > > I get this error when i also use COMMIT, ROLLBACK.....does this mean a > patch is missing ? > > Thanks > Sam Ok i realised that the only way data can be committed within a procedure is to use nested BEGIN......END. For example: BEGIN statement1 BEGIN statement2 END END so if the statement2 fails data is rolledback only until the inner BEGIN. In other words statement1 changes is retained. In my case i use a for loop and update data row by row: BEGIN FOR every record in CURSOR UPDATE DATA for the row END FOR END Since i have large amounts of data, if any error occured the entire transaction was rolled back. The solution for this would be: BEGIN FOR every record in CURSOR UPDATE() END FOR END FUNCTION UPDATE () BEGIN UPDATE statement EXCEPTION END when one record fails data only for that is rolled back the rest of the data is saved. EXCEPTION has to be caught. Hope this helps anyone else facing similar issues. Sam
sam <sam.mahindrakar@gmail.com> writes:
> Ok i realised that the only way data can be committed within a
> procedure is to use nested BEGIN......END.
Nothing that you said in this message is correct. You can't "commit"
anything within a function, and bare BEGIN/END don't do anything at
all except create a syntactic grouping.
BEGIN/EXCEPT/END can indeed be used to limit the scope of errors,
but I don't think the way you've described it is a helpful way
to think about it, even with that correction.
regards, tom lane
sam wrote: > FUNCTION UPDATE () > BEGIN > UPDATE statement > > EXCEPTION > END > > > when one record fails data only for that is rolled back the rest of > the data is saved. EXCEPTION has to be caught. > > As I recently found out, too many savepoints really kill PostgreSQL's performance in a transaction. A function inserting/updating, say, 100,000 records will perform OK, but statements run in the same transaction after the function completes will be very slow. So ... if you find that after your big update statement performance is terrible, you might need to explicitly check the conditions that might result in an exception and skip those records, thus avoiding the EXCEPTION block. -- Craig Ringer