Abort transaction due to error

[samantha1918]

I have a weird problem.....the database iam handling has huge amount of data....when i run my program on the cursor containing the data i run into the following error: 54000 PROGRAM LIMIT EXCEEDED.
I understand that in a single transaction there are too many queries being executed.I want to know if i can solve this problem by using nested BEGIN...END.
Postgresql does not roolback everything when an error occurs. Is there any way to go about this problem??

Thanks
Sam

[rski]

Maybe sevepoints would help, see here

http://www.postgresql.org/docs/current/static/sql-savepoint.html

[samantha1918]

ok i tried using save points but it i get the following error:
ERROR: SPI_execute_pla n failed executing query "SAVEPOINT my_savepoint": SPI_ERROR_TRANS ACTION

Iam using postgresql 8.1. i think postgres verions >8 support savepoints?do they?
And does commit work as a command in postgres?if not what can be a workaround?

Thanks again
Sam

[rski]

see here
Maybe you should apply some patch. What 8.1 version do you use

[samantha1918]

see here
Maybe you should apply some patch. What 8.1 version do you use

sorry i wrote the version as 8.1 earlier.
Iam using version "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"

Thanks Sam

[samantha1918]

Also in a procedure when i try writing something like this:
result := test_case1(lane ,thePartition);
IF result=1 THEN
PREPARE TRANSACTION 'foo';
PERFORM getandsetimpute ddata(thePartit ion);
COMMIT PREPARED 'foo';
END IF;


i get the following error:
ERROR: SPI_execute_pla n failed executing query "PREPARE TRANSACTION 'foo'": SPI_ERROR_TRANS ACTION
SQL state: XX000

What does this error mean?iam writing it wrong or some pacth is missing?

Thanks for your time
Sam

[samantha1918]

Also in a procedure when i try writing something like this:
result := test_case1(lane ,thePartition);
IF result=1 THEN
PREPARE TRANSACTION 'foo';
PERFORM getandsetimpute ddata(thePartit ion);
COMMIT PREPARED 'foo';
END IF;


i get the following error:
ERROR: SPI_execute_pla n failed executing query "PREPARE TRANSACTION 'foo'": SPI_ERROR_TRANS ACTION
SQL state: XX000

What does this error mean?iam writing it wrong or some pacth is missing?

Thanks for your time
Sam

I realised the solution to avoid total rollback of a transaction would be using nested BEGIN......END

Sam