Thread: [GENERAL] Auto-Rollback option

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

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 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

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