Re: [GENERAL] Trouble with Savepoints in postgres
On Mar 12, 3:31 pm, sam [EMAIL PROTECTED] wrote: On Mar 12, 8:11 am, [EMAIL PROTECTED] (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, [EMAIL PROTECTED] (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 ([EMAIL PROTECTED]) 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with Savepoints in postgres
sam [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with Savepoints in postgres
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with Savepoints in postgres
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, [EMAIL PROTECTED] (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-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with Savepoints in postgres
On Mar 12, 8:11 am, [EMAIL PROTECTED] (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, [EMAIL PROTECTED] (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 ([EMAIL PROTECTED]) 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with Savepoints in postgres
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
