December 3rd, 2002, 07:28 PM
I am having a slight problem with PostGreSQL/plpgsql. Specifically:
1. Exception Handling
2. Internal Commit/Rollback
My understanding is that every function being executed is either inside an explicit or implicit transaction. Commit/Rollback can only be executed at the top level (i.e. by the controlling application, rather than within the function).
This is a real disincentive for me to use PostGreSQL. I have written some fairly detailed stored procedures in Oracle (2.5K lines of code including sub-procedures), but would not be able to consider using PostGreSQL in a similar fashion because of the lack of Exception Handling.
With Exception Handling, you can rollback on an error, but log (and commit) information about the error to an error log table. This is enormously helpful for troubleshooting purposes when such errors occur. Exceptions are also awesome because it enables you to write code that fails gracefully rather than having to
anticipate and code for every possible thing that can go wrong.
I can think of two scenarios when it would be quite helpful:
1. Large Scale Data processing.
A large transaction eats alot of resources before it is committed. Even worse, if an exception occurs , the 100,000 rows that have already successfully been processed must be rolled back just because a text field was too long for the column that it was being inserted into *in an unrelated row*. Being able to commit every 10,000 rows or so would vastly reduce the headache when something goes wrong, as well as the load on rollback segments.
2. General Programming.
I like to write code that fails gracefully. Ideally, it will be able to handle unanticipated errors as well as anticipated ones and keep working, rather than just fall over. To do this, it needs Exception Handling. Ideally, supporting commit and rollback within functions would mean that information about the error (or where it occurred) can be stored, which makes it much easier to debug.
Am I being silly? Does anyone have an eta on these features?
If there is a way to achieve the functionality I am after without relying on these features then please enlighten me.
December 3rd, 2002, 09:12 PM
Have you checked out the latest version of PostgreSQL, which just came out? It's a major release, and there have been many advancements. See the details here.
As I understand it, PostgreSQL still does not allow for nested transactions, but that is on the way soon.
Also, there is work being done to allow Java as a procedural language, which will definitely give you some nice exception handling.
Meanwhile, you might consider the fact that PostgreSQL supports several procedural languages already. Try Perl; it is malleable enough that you can make it do just about anything, if you put your mind to it.
And of course, there is always Python, although I have never messed with it.
December 3rd, 2002, 09:58 PM
Thanks for the reply. Yeah, 7.3 looks pretty good. I particularly like the function privileges and multiple schemas.
Perhaps I'm not smart enough to understand nested transactions. From my browsing of the mailing lists it seems like this is what is proposed:
still more stuff;
This doesn't make much sense to me. What happens if an exception occurs in 'still more stuff'? Is it all rolled back? Is 'more stuff' committed and the rest discarded? What happens if the outer transaction is a rollback instead of a commit? Is it all rolled back?
The way that I had envisaged this working is along this line:
commit; begin work;
rollback; begin work;
commit; begin work;
do other stuff;
Now if an exception occurs during 'do stuff', then we'd roll it back. If it went through fine, commit it. Whether 'do other stuff' is committed or rolled back, 'do stuff' gets committed.
The session would only ever be in one transaction, but you could commit/rollback the transaction (and instantly start another one) within your code.
With regards to procedural languages, I've been pretty much just sticking to plpgsql, sql and C libraries. But I'll have a look at plperl and see what it's like.