#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12

    Exception Handling


    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.

    Cheers,
    Merl
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    merl,

    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12
    Hey rycamor,

    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:
    begin work;
    stuff;
    begin work;
    more stuff;
    commit;
    still more stuff;
    commit;

    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:
    begin work;
    try
    do stuff;
    commit; begin work;
    except
    rollback; begin work;
    log stuff;
    commit; begin work;
    end;

    do other stuff;
    commit;

    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.

    Merl

IMN logo majestic logo threadwatch logo seochat tools logo