On Mar 12, 3:31 pm, sam <sam.mahindra...@xxxxxxxxx> wrote: > On Mar 12, 8:11 am, alvhe...@xxxxxxxxxxxxxx (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...@xxxxxxxxxxxxxxxxx (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...@xxxxxxxxxxxxxx) > > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
