Re: Fwd: [NOVICE] Autocommit in Postgresql

I personally prefer it the way it is. I am not migrating from Oracle,
which could be the reason. If I want a transaction, I specify it. If I
don't want a transaction I don't specify it.

I think a lot more people would have issues if they run a couple dozen
queries and then turn it off and forget to commit, or their system crashes.

If you were going to implement something like this, I would like to
see it as an option that is off by default.

In any case, he has to do a rollback after his statement fails. I
don't think it is that much to expect a begin at the same time. If he
forgot the rollback and tried it it would probably give him very
skewed results and he would need to spend a lot of time trying to
figure out what was wrong with the function.

Sim
________________________________________________________________________________

I'll give you an example.

I am debugging a certain function named do_lots_stuff();

So I write the code like that:

rollback;
begin;
select do_lots_stuff();

And I can freely run it a few times. However usually this is not that
simple. In debugging a certain function/triggers I have to run many
different select/update/delete from different tables until I am sure
all works fine so I end up selecting queries first the "rollback;
begin;" then the different queries that I need to check, usually in
different order every time.

However, as soon as one of the queries produces an error (or as soon
as I want to restart the process) I have to run the "rollback; begin;"
line again... And woe to me if by mistake I select only rollback; and
forget the begin; because the next time I run a single delete/update
line it will autocommit.

I agree that theoretically I could order the lines in a nice order and
just keep pressing F5, but usually it is easier to say that to do.
Especially when you need to run multiple queries to check the results
of each.

In Oracle (or more specifially "SQL Navigator" which is a client to
Oracle) each new query window automatically starts a transaction and
ALL the changes you do are recorder but never commited until you press
a specified button (commit ro rollback). Also, on errors I am not
forced to press any button, it just automatically rollbacks (as I have
no other choice anyway).

On 5/12/05, Andreas Pflug <pgadmin(at)pse-consulting(dot)de> wrote:
> Vitaly Belman wrote:
>
> >
> >>That's the problem... Is there a way to make it implicitly open a
> >>transaction? For example now in pgAdmin when I play with SQLs I have
> >>to do begin and rollback constant because whenever a query fails it
> >>refuses to do anything at all until I "rollback", and then of course I
> >>have to do "begin" again unless I want my queries to be real.
>
> Apparently I don't really understand the problem. If you don't
> explicitely open a transaction, everything you execute within one
> execute command will be processed within one transaction by the backend.
> If you want to execute step by step, just mark the commands and hit F5.
>
> > (I am not sure if any of them read this list).
>
> At least I am not on the list, so please continue crossposting if
> applicable.
>
> Regards,
> Andreas
>

--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent(at)hotmail(dot)com
Yahoo!: VitalyBe

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend