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

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;

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

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

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

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

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