Re: Trouble with Savepoints in postgres

On Mar 12, 3:31 pm, sam <sam(dot)mahindra(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> On Mar 12, 8:11 am, alvhe(dot)(dot)(dot)(at)alvh(dot)no-ip(dot)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(dot)(dot)(dot)(at)commandprompt(dot)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(dot)(dot)(dot)(at)postgresql(dot)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