Re: Trouble with Savepoints in postgres - Mailing list pgsql-general

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