Thread: [GENERAL] Auto-Rollback option
Hi,
is there any way to set Auto-Rollback : ON, automaticly during instalation process or using query (maybe something like set autocommit = 'on')?
We need it to automaticly close the transaction if an error occures during implementing patches.
Thanks in advanced for the answear.
Best regards,
Malgorzata Pomykacz
is there any way to set Auto-Rollback : ON, automaticly during instalation process or using query (maybe something like set autocommit = 'on')?
We need it to automaticly close the transaction if an error occures during implementing patches.
Thanks in advanced for the answear.
Best regards,
Malgorzata Pomykacz
On Mon, Feb 13, 2017 at 02:55:03PM +0100, Małgorzata Hubert wrote: > is there any way to set Auto-Rollback : ON, automaticly during instalation > process or using query (maybe something like set autocommit = 'on')? > We need it to automaticly close the transaction if an error occures during > implementing patches. Auto-Rollback is the implicit default. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 02/13/2017 05:55 AM, Małgorzata Hubert wrote: > Hi, > is there any way to set Auto-Rollback : ON, automaticly during > instalation process or using query (maybe something like set autocommit > = 'on')? > We need it to automaticly close the transaction if an error occures > during implementing patches. How are you applying the patches? With what library/interface/etc? For instance in psql you have: https://www.postgresql.org/docs/9.6/static/app-psql.html "AUTOCOMMIT When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost. Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. " NOTE that you have to explicitly ROLLBACK a failed transaction though. > > Thanks in advanced for the answear. > Best regards, > Malgorzata Pomykacz -- Adrian Klaver adrian.klaver@aklaver.com
Take a look at ON_ERROR_STOP variable.
\set ON_ERROR_STOP 1
On 13/02/2017 15:55, Małgorzata Hubert wrote:
\set ON_ERROR_STOP 1
On 13/02/2017 15:55, Małgorzata Hubert wrote:
Hi,
is there any way to set Auto-Rollback : ON, automaticly during instalation process or using query (maybe something like set autocommit = 'on')?
We need it to automaticly close the transaction if an error occures during implementing patches.
Thanks in advanced for the answear.
Best regards,
Malgorzata Pomykacz
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
So my problem is like this: I start the transaction with BEGIN TRANSACTION; Then I have for example some INSERTs to DB and at the end COMMIT; and END TRANSACTION; But if one of this INSERTs causes error, the transaction will stop (but it is still open and next patch is implemented within the same transaction). When I turn the Auto-Rollback on everything is ok : in situation like this my transaction is automatically rollbacked and closed. But I do not want to do the Auto-Rollback click manualy in the Option menu or query editor window (because I can not be sure that the person who will run the patch would remember about this click). I'm using 1.22.1 version. -- View this message in context: http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5943950.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2/13/2017 7:15 AM, mpomykacz wrote: > So my problem is like this: > > I start the transaction with BEGIN TRANSACTION; > Then I have for example some INSERTs to DB > and at the end COMMIT; and END TRANSACTION; COMMIT ends the transaction. In PostgreSQL, END TRANSACTION is redundant, equivalent to COMMIT, do one or the other, not both. > > But if one of this INSERTs causes error, the transaction will stop (but it > is still open and next patch is implemented within the same transaction). Patch ? > > When I turn the Auto-Rollback on everything is ok : in situation like this > my transaction is automatically rollbacked and closed. there is no autorollback option in standard PostgreSQL ? > > But I do not want to do the Auto-Rollback click manualy in the Option menu > or query editor window (because I can not be sure that the person who will > run the patch would remember about this click). option? query editor window? what software are you talking about? > I'm using 1.22.1 version. 1.22.1 version? PostgreSQL versions currently supported are 9.2.x to 9.6.x -- john r pierce, recycling bits in santa cruz
On 02/13/2017 09:59 AM, John R Pierce wrote: > On 2/13/2017 7:15 AM, mpomykacz wrote: >> So my problem is like this: >> >> I start the transaction with BEGIN TRANSACTION; >> Then I have for example some INSERTs to DB >> and at the end COMMIT; and END TRANSACTION; > > COMMIT ends the transaction. In PostgreSQL, END TRANSACTION is > redundant, equivalent to COMMIT, do one or the other, not both. > >> >> But if one of this INSERTs causes error, the transaction will stop >> (but it >> is still open and next patch is implemented within the same transaction). > > Patch ? Pretty sure the OP is applying a series of SQL driven patches to add/change/remove database schema. > >> >> When I turn the Auto-Rollback on everything is ok : in situation like >> this >> my transaction is automatically rollbacked and closed. > > there is no autorollback option in standard PostgreSQL ? > >> >> But I do not want to do the Auto-Rollback click manualy in the Option >> menu >> or query editor window (because I can not be sure that the person who >> will >> run the patch would remember about this click). > > option? query editor window? what software are you talking about? I would say pgAdmin3: https://www.pgadmin.org/download/source.php > >> I'm using 1.22.1 version. > > 1.22.1 version? PostgreSQL versions currently supported are 9.2.x to See above. > 9.6.x > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Feb 13, 2017 at 1:10 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/13/2017 09:59 AM, John R Pierce wrote:On 2/13/2017 7:15 AM, mpomykacz wrote:So my problem is like this:
I start the transaction with BEGIN TRANSACTION;
Then I have for example some INSERTs to DB
and at the end COMMIT; and END TRANSACTION;
COMMIT ends the transaction. In PostgreSQL, END TRANSACTION is
redundant, equivalent to COMMIT, do one or the other, not both.
But if one of this INSERTs causes error, the transaction will stop
(but it
is still open and next patch is implemented within the same transaction).
Patch ?
Pretty sure the OP is applying a series of SQL driven patches to add/change/remove database schema.
When I turn the Auto-Rollback on everything is ok : in situation like
this
my transaction is automatically rollbacked and closed.
there is no autorollback option in standard PostgreSQL ?
But I do not want to do the Auto-Rollback click manualy in the Option
menu
or query editor window (because I can not be sure that the person who
will
run the patch would remember about this click).
option? query editor window? what software are you talking about?
I would say pgAdmin3:
https://www.pgadmin.org/download/source.php I'm using 1.22.1 version.
1.22.1 version? PostgreSQL versions currently supported are 9.2.x to
See above.9.6.x
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
1.22.1 version refers to PgAdmin3. It is not the PostgreSQL version.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On 02/13/2017 09:59 AM, John R Pierce wrote: > On 2/13/2017 7:15 AM, mpomykacz wrote: >> So my problem is like this: >> >> I start the transaction with BEGIN TRANSACTION; >> Then I have for example some INSERTs to DB >> and at the end COMMIT; and END TRANSACTION; > > COMMIT ends the transaction. In PostgreSQL, END TRANSACTION is > redundant, equivalent to COMMIT, do one or the other, not both. > >> >> But if one of this INSERTs causes error, the transaction will stop >> (but it >> is still open and next patch is implemented within the same transaction). > > Patch ? > >> >> When I turn the Auto-Rollback on everything is ok : in situation like >> this >> my transaction is automatically rollbacked and closed. > > there is no autorollback option in standard PostgreSQL ? Forgot to add, in pgAdmin3 there is: https://www.pgadmin.org/docs/1.22/options-query_tool.html?highlight=rollback Enable Auto ROLLBACK - Check the box next to Enable Auto ROLLBACK to instruct the query tool to execute a ROLLBACK if a query fails. > >> >> But I do not want to do the Auto-Rollback click manualy in the Option >> menu >> or query editor window (because I can not be sure that the person who >> will >> run the patch would remember about this click). > > option? query editor window? what software are you talking about? > >> I'm using 1.22.1 version. > > 1.22.1 version? PostgreSQL versions currently supported are 9.2.x to > 9.6.x > > -- Adrian Klaver adrian.klaver@aklaver.com
Il 13/02/2017 18:59, John R Pierce ha scritto: > option? query editor window? what software are you talking about? > >> I'm using 1.22.1 version. > > 1.22.1 version? PostgreSQL versions currently supported are 9.2.x > to 9.6.x > > I think he's talking about pgAdmin III Cheers Moreno
On Mon, Feb 13, 2017 at 1:19 PM, Moreno Andreo <moreno.andreo@evolu-s.it> wrote:
Il 13/02/2017 18:59, John R Pierce ha scritto:option? query editor window? what software are you talking about?I think he's talking about pgAdmin IIII'm using 1.22.1 version.
1.22.1 version? PostgreSQL versions currently supported are 9.2.x to 9.6.x
Cheers
Moreno
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
In PgAdmin III, AUTO ROLLBACK and AUTO COMMIT are on by default in version 1.22.1
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Yes, I'm talking about pgAdmin III - sorry... I think that auto-commit is on on default but auto-rollback is off. But I'll check if you say so. And I know I can check the box next to Enable Auto ROLLBACK but I'm trying to avoid it and enable auto rollback not by a manual way. -- View this message in context: http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5944047.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/13/2017 02:10 PM, mpomykacz wrote: > Yes, I'm talking about pgAdmin III - sorry... > > I think that auto-commit is on on default but auto-rollback is off. But I'll > check if you say so. Did you look here: https://www.pgadmin.org/docs/1.22/options-query_tool.html It seems checking it here would make the choice persist between sessions. This assumes that everyone uses the same instance of pgAdmin3. Otherwise it would need to be checked on each instance. > > And I know I can check the box next to Enable Auto ROLLBACK but I'm trying > to avoid it and enable auto rollback not by a manual way. > > > > -- > View this message in context: http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5944047.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Ok, thanks for the answers. But unfortunatelly they did not solve my problem. I will move it to the pgadmin subforum. Thanks:) -- View this message in context: http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5944159.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/14/2017 05:12 AM, mpomykacz wrote: > Ok, thanks for the answers. But unfortunatelly they did not solve my problem. Still not actually sure what the issue is?: 1) Problem with pgAdmin setup or 2) Broader issue of having Postgres rollback automatically on a error. or 3) Patch management. > I will move it to the pgadmin subforum. > Thanks:) > > > > -- > View this message in context: http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5944159.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com