Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: handling errors of transactions in functions

    hello, I'm starting with Postgre and I cant find how to handle errors in a function. I Know how to do this in Ms-sql but not in postgre.

    In mssql:
    ----------------
    begin transaction
    insert into [my talbe]...
    if (@@error <> 0)
    begin
    rollback transaction
    raiseerror('Some error has occur', 16, 1)
    return @@error
    end

    in Postresql:
    -------------------
    begin transaction
    insert into [my talbe]...
    ????

  2. #2
    Join Date
    Jan 2004
    Posts
    26
    One more time I will answer my own question only for the ones that can be interested in the subject.
    Well, I didn't find a solution for my problem and all that I found it's that Postgre procedural language it's very limited.

  3. #3
    Join Date
    Jan 2004
    Posts
    5

    Re: handling errors of transactions in functions

    Hi,

    did you consider doing the check for an error in your application?
    e.g. in java you get an error when your statement is not correct or raises an error.

    Greetz


    Originally posted by Margodth
    hello, I'm starting with Postgre and I cant find how to handle errors in a function. I Know how to do this in Ms-sql but not in postgre.

    In mssql:
    ----------------
    begin transaction
    insert into [my talbe]...
    if (@@error <> 0)
    begin
    rollback transaction
    raiseerror('Some error has occur', 16, 1)
    return @@error
    end

    in Postresql:
    -------------------
    begin transaction
    insert into [my talbe]...
    ????

  4. #4
    Join Date
    Jan 2004
    Posts
    26

    Re: handling errors of transactions in functions

    Ok, but this is not server-side. If I want to create a function that makes multiples inserts and updates and I will begin the function with a BEGIN TRANSACTION. If one of the inserts or updates ends with an error, how I do to determine that I need to do a rollback?


    Originally posted by Gruffi
    Hi,

    did you consider doing the check for an error in your application?
    e.g. in java you get an error when your statement is not correct or raises an error.

    Greetz

  5. #5
    Join Date
    Jan 2004
    Posts
    19

    Re: handling errors of transactions in functions

    Good question. Let me know when you find out the answer. The PostgreSQL server-side PLs have poor exception handling. I haven't seen any catches. However, I'm not sure if you need to do a rollback because when the function errors it probably doesn't commit. I'm not sure about this but you might want to test it out.

    Originally posted by Margodth
    Ok, but this is not server-side. If I want to create a function that makes multiples inserts and updates and I will begin the function with a BEGIN TRANSACTION. If one of the inserts or updates ends with an error, how I do to determine that I need to do a rollback?

  6. #6
    Join Date
    Jan 2004
    Posts
    5

    Re: handling errors of transactions in functions

    ok. I think we just move the problem from the database to your client app.
    for client access I created a class that manages the querys and does a little error-handling.
    so i just do:

    DBPostgreSQL db = new DBPostgreSQL(); //new class
    db.connect("./myconfig.cfg"); //connect to your db
    db.executeQuery(UPDATE, "insert into...."; //
    .
    .
    .
    if (db.hasErrors)
    db.RollbackTransaction()
    else
    db.CommitTransaction();

    db.disconnect();

    what do you think about this?
    I already managed to write an app that handles concurrent updates from multiple users on one database.

    Greetz Gruffi

  7. #7
    Join Date
    Jan 2004
    Posts
    26

    Re: handling errors of transactions in functions

    It's ok, if the function ends with an error, then it never commits, but the question is if a rollback it's performed. It's very important. An open transaction that never ends, means locking that will not unlock

    Originally posted by vidmas
    Good question. Let me know when you find out the answer. The PostgreSQL server-side PLs have poor exception handling. I haven't seen any catches. However, I'm not sure if you need to do a rollback because when the function errors it probably doesn't commit. I'm not sure about this but you might want to test it out.

  8. #8
    Join Date
    Jan 2004
    Posts
    26

    Re: handling errors of transactions in functions

    well, it looks rigth to me. You're progamming with...?

    I do not agree to change the subject. The discussion of how to build a class it's very important but this it's a very different topic. This is Server-side and not Client-side. Why? Because Sever-side it's the idea of every script language of any RDBMS.


    Originally posted by Gruffi
    ok. I think we just move the problem from the database to your client app.
    for client access I created a class that manages the querys and does a little error-handling.
    so i just do:

    DBPostgreSQL db = new DBPostgreSQL(); //new class
    db.connect("./myconfig.cfg"); //connect to your db
    db.executeQuery(UPDATE, "insert into...."; //
    .
    .
    .
    if (db.hasErrors)
    db.RollbackTransaction()
    else
    db.CommitTransaction();

    db.disconnect();

    what do you think about this?
    I already managed to write an app that handles concurrent updates from multiple users on one database.

    Greetz Gruffi

  9. #9
    Join Date
    Jan 2004
    Posts
    5

    Re: handling errors of transactions in functions

    i do not really know how to do this on the server side. wanted to provide another view on the problem. i know it works in some of my apps.
    if you get another idea please post a comment or send a mail.

    greetz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •