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)
Import Notes
Reply to msg id not found: b62b6839-55aa-4856-b78b-2b5934fa3c80@p73g2000hsd.googlegroups.com
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-generalAlso can u please tell mewhy i get the error...ERROR: SPI_execute_plan
failed executing query "PREPARE TRANSACTION 'foo'":
SPI_ERROR_TRANSACTIONI 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 statementEXCEPTION
ENDwhen 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