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

    Unanswered: MSSQL to PostgreSql

    Hello, I'm just a newbie in PostgreSql but not newbie with MSSQL. As I don't understand Postgre I will really need some help to find out some things. While building system I use Stored Procedures, all my questions will be pointing to PL/pgSQL or related.

    What I can't find is what to do with a transaction when trapping an error.
    So to make a complete atomic transaction I have to use
    Code:
    BEGIN;
    
    BEGIN
        Update myTable set myField = null;
        Update mySecondTable set myField = null;
        COMMIT;
    EXCEPTION
        WHEN condition [ I don't know the error yet ] THEN
        ROLLBACK;
    END;
    while in a not complete atomic transaction I may change some lines of the example above.

    Code:
    BEGIN;
    
    BEGIN
        Update myTable set myField = null;
        SAVEPOINT my_savepoint;
        Update mySecondTable set myField = null;
        COMMIT;
    EXCEPTION
        WHEN condition [ I don't know the error yet ] THEN
        ROLLBACK my_savepoint;
    END;
    I understand that the first condition of the exption it's not optinoal, so what condition I will use if I really don't know all the erros I can have, just beacuse the nature of the word error.

    How is supposed to work with all this?
    What about nested transactions, they exists or are replaced by savepoints?
    What about transactions with functions calls?

    Thanks

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I hope some other folks will address some of your more complex questions. My input is that it seems you are handling rollbacks manually, and PostgreSQL does a lot of this automatically. Any error in a transaction block results in a rollback.

  3. #3
    Join Date
    Jan 2004
    Posts
    26
    Quote Originally Posted by ByteRyder52
    My input is that it seems you are handling rollbacks manually, and PostgreSQL does a lot of this automatically. Any error in a transaction block results in a rollback.
    That's what I thought but I was not sure. So if I'm working in a big function (stored procedure) and a Sql command like insert, update or delete fails, a rollback it's performed automatically?

    Now I have so many questions... where can I find more detailed information about transactions?
    Last edited by Margodth; 01-10-06 at 10:55.

Posting Permissions

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