Обсуждение: Fwd: [NOVICE] Autocommit in Postgresql
Anyone has an idea? On the subject?
---------- Forwarded message ----------
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: May 12, 2005 5:43 PM
Subject: Re: [NOVICE] Autocommit in Postgresql
To: Vitaly Belman <vitalyb@gmail.com>
Cc: Olivier Thauvin <olivier.thauvin@aerov.jussieu.fr>, Postgresql
Novice <pgsql-novice@postgresql.org>
Vitaly Belman <vitalyb@gmail.com> writes:
> 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.
pgAdmin really should offer some kind of setting to help you with that.
Try asking the pgadmin gurus (I am not sure if any of them read this
list).
regards, tom lane
-- ICQ: 1912453AIM: VitalyB1984MSN: tmdagent@hotmail.comYahoo!: VitalyBe
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
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@pse-consulting.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: 1912453AIM: VitalyB1984MSN: tmdagent@hotmail.comYahoo!: VitalyBe
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@pse-consulting.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: 1912453AIM: VitalyB1984MSN: tmdagent@hotmail.comYahoo!: VitalyBe ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend