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/curre...savepoint.html

samantha1918

ok i tried using save points but it i get the following error:
ERROR: SPI_execute_plan failed executing query "SAVEPOINT my_savepoint": SPI_ERROR_TRANSACTION

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

Quote:

Originally Posted by rski

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 getandsetimputeddata(thePartition);
COMMIT PREPARED 'foo';
END IF;

i get the following error:
ERROR: SPI_execute_plan failed executing query "PREPARE TRANSACTION 'foo'": SPI_ERROR_TRANSACTION
SQL state: XX000

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

Thanks for your time
Sam

samantha1918

Quote:

Originally Posted by 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 getandsetimputeddata(thePartition);
COMMIT PREPARED 'foo';
END IF;

i get the following error:
ERROR: SPI_execute_plan failed executing query "PREPARE TRANSACTION 'foo'": SPI_ERROR_TRANSACTION
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

Abort transaction due to error

re: Abort transaction due to error

re: Abort transaction due to error

re: Abort transaction due to error

re: Abort transaction due to error

re: Abort transaction due to error

re: Abort transaction due to error

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about