Re: Fwd: [NOVICE] Autocommit in Postgresql

From: Vitaly Belman <vitalyb(at)gmail(dot)com>
To: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: Fwd: [NOVICE] Autocommit in Postgresql
Date: 2005-05-14 11:02:17
Message-ID: fa96e3c60505140402452deb61@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-novice

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

In response to

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Sim Zacks 2005-05-15 06:11:32 Re: Fwd: [NOVICE] Autocommit in Postgresql
Previous Message Andreas Pflug 2005-05-12 15:25:01 Re: Fwd: [NOVICE] Autocommit in Postgresql

Browse pgsql-novice by date

  From Date Subject
Next Message John DeSoi 2005-05-14 11:55:54 Re: Starting the Database
Previous Message ann hedley 2005-05-14 10:44:40 Can null values be sorted low?