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

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12

    question about start transactions


    Hi all,

    I'm familiarising myself with postgresql syntax, and am running in some trial scripts. As a matter of course I always end with a commit or rollback (rollback while developing script, commit for actual execution).

    I ran the following:
    insert into test (test_id) values (1);
    commit;

    And it inserts the row, but committing gives the message "COMMIT: no transaction in progress".

    When running:
    insert into test (test_id) values (2);
    rollback;
    The row is not present during a subsequent query, but the message "ROLLBACK: no transaction in progress" is given.

    So, the behaviour is what I would have expected, but the message seems counter intuitive (in particular, it looks like the rollback has not taken place).

    Is there some setting that needs to be tweaked to give more meaningful messages, or is this just something to live with, and I'll take it on trust that postgresql is doing what I *think* it should be doing?

    Cheers,
    Merl
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Arrow


    You're almost there, just need to tell Postgres you are starting a transaction. Oracle lets you commit and rollback without the BEGIN stuff, but apparently Postgres requires the BEGIN statement before you can use COMMIT or ROLLBACK.


    Do the transaction like so:

    begin work;
    insert into test (test_id) values (1);
    commit;
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 1999
    Location
    Seattle
    Posts
    133
    Rep Power
    15
    You can also use END in place of the COMMIT. END is a synonym of COMMIT, for those who want their SQL to appear in nicely grouped blocks.

    Note that without BEGIN/COMMIT (or END) statements, Postgres uses an auto-commit mode, that is, every one of your statements acts as though it has its own BEGIN/COMMIT wrapper. If, for example, you have multiple INSERT statements, wrapping in BEGIN/COMMIT can substantially decrease execution time.

    Don
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12
    Thanks Ted,

    That was exactly the sort of response I was expecting. I'll use "begin work;" to explicitly start transactions in future.

    Donarb, your comments confused me slightly. I understand you to say that without BEGIN/COMMIT it implicitly commits every statement. Certainly, you should ALWAYS use commit/rollback in conjunction with dml statements. But in my experience (see initial post), rollback still works correctly (i.e. as expected) even if the transaction is not explicitly started.

    Merl
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 1999
    Location
    Seattle
    Posts
    133
    Rep Power
    15
    Postgres considers every statement outside of a BEGIN/COMMIT block as an individual transaction.

    So, as I mentioned before, each statement will auto-commit, if successful. This also means that each statement will auto-rollback, if unsuccessful. This is why your COMMIT and ROLLBACK did not work, they had already completed.

    Note that this feature is not a part of the SQL standard, but a few database vendors offer it.

    Don
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12
    Ok,

    Thanks donarb for clearing that up. I make a mistake in my tests, and thought that it was rolling back successfully, just giving a screwy message as it did so. I just checked again, and the rollback is not occurring.

    I understand now, no rollback possible unless within an explicitly started transaction.

    Cheers,
    Merl
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 1999
    Location
    Seattle
    Posts
    133
    Rep Power
    15
    Just to be clear, a rollback IS possible, that is, for a single statement not inside of a transaction. If the statement fails, it will rollback.

    Don

IMN logo majestic logo threadwatch logo seochat tools logo