Thread: turn off auto-commit

turn off auto-commit

From
"Waruna Geekiyanage"
Date:
How to turn off auto-commit in pgsql 7.2?

Re: turn off auto-commit

From
"Nick Fankhauser"
Date:
>How to turn off auto-commit in pgsql 7.2?

\set autocommit off
\set autocommit on

Re: turn off auto-commit

From
Bruce Momjian
Date:
There is no option in 7.2, but it will appear in 7.3, due out in a few
months.


---------------------------------------------------------------------------

Waruna Geekiyanage wrote:
> How to turn off auto-commit in pgsql 7.2?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: turn off auto-commit

From
Bruce Momjian
Date:
No, they are unrelated.

---------------------------------------------------------------------------

Waruna Geekiyanage wrote:
> Does turn fsync off equivalent to turn autocommit off?
>
>
> ----- Original Message -----
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> To: Waruna Geekiyanage <waruna@nirmani.com>
> Cc: <pgsql-admin@postgresql.org>
> Sent: Saturday, August 31, 2002 6:48 AM
> Subject: Re: [ADMIN] turn off auto-commit
>
>
> >
> > There is no option in 7.2, but it will appear in 7.3, due out in a few
> > months.
> >
> >
> > --------------------------------------------------------------------------
> -
> >
> > Waruna Geekiyanage wrote:
> > > How to turn off auto-commit in pgsql 7.2?
> >
> > --
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 359-1001
> >   +  If your life is a hard drive,     |  13 Roberts Road
> >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073
> >
> >
>
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: turn off auto-commit

From
"Waruna Geekiyanage"
Date:
Does turn fsync off equivalent to turn autocommit off?


----- Original Message -----
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Waruna Geekiyanage <waruna@nirmani.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Saturday, August 31, 2002 6:48 AM
Subject: Re: [ADMIN] turn off auto-commit


>
> There is no option in 7.2, but it will appear in 7.3, due out in a few
> months.
>
>
> --------------------------------------------------------------------------
-
>
> Waruna Geekiyanage wrote:
> > How to turn off auto-commit in pgsql 7.2?
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
19073
>
>



Re: turn off auto-commit

From
Bruce Momjian
Date:
Waruna Geekiyanage wrote:
> Does turn fsync off equivalent to turn autocommit off?

No, fsync is related to flushing information to disk. It doesn't relate
to autocommit.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: turn off auto-commit

From
Björn Lundin
Date:
Bruce Momjian wrote:

> Waruna Geekiyanage wrote:
>> Does turn fsync off equivalent to turn autocommit off?
>
> No, fsync is related to flushing information to disk. It doesn't relate
> to autocommit.
>
This auto-commit on/off, is this what I think it is;
a way off NOT needing to enclose several statements in a begin/commit,
but having a new transaction directly after a commit, in psql, like
Oracle's sql*plus?

If so, in what version was this introduced, or is this mentioned somewhere
on the web?

regards
/Björn Lundin

Re: turn off auto-commit

From
"Nick Fankhauser"
Date:

> like
> Oracle's sql*plus?

Yes, it seems to act just like sql*plus in this regard.

> If so, in what version was this introduced, or is this mentioned somewhere
> on the web?

I don't remember it ever not being there. I've been a user since version
6.5.

-Nick


Re: turn off auto-commit

From
Bruce Momjian
Date:
Bj�rn Lundin wrote:
> Bruce Momjian wrote:
>
> > Waruna Geekiyanage wrote:
> >> Does turn fsync off equivalent to turn autocommit off?
> >
> > No, fsync is related to flushing information to disk. It doesn't relate
> > to autocommit.
> >
> This auto-commit on/off, is this what I think it is;
> a way off NOT needing to enclose several statements in a begin/commit,
> but having a new transaction directly after a commit, in psql, like
> Oracle's sql*plus?
>
> If so, in what version was this introduced, or is this mentioned somewhere
> on the web?

Yes, autocommit appears in 7.3, due out in a few months.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: turn off auto-commit

From
Björn Lundin
Date:
Bruce Momjian wrote:


> Yes, autocommit appears in 7.3, due out in a few months.

A very nice feature, thanks.

/Björn


Re: turn off auto-commit

From
"Nick Fankhauser"
Date:
> Bruce Momjian wrote:
>
> Yes, autocommit appears in 7.3, due out in a few months.

I'm confused.

Isn't autocommit already there & on by default in psql currently?

-Nick

Re: turn off auto-commit

From
Bruce Momjian
Date:
Nick Fankhauser wrote:
>
> > Bruce Momjian wrote:
> >
> > Yes, autocommit appears in 7.3, due out in a few months.
>
> I'm confused.
>
> Isn't autocommit already there & on by default in psql currently?

He wants to turn autocomit off:

    $ psql
    test=> UPDATE tab SET col = 1;
    test=> UPDATE tab2 SET col = 2;
    test=> COMMIT;

With autocommit off, you basically start in a multi-statement
transaction at the start and every time you commit.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: turn off auto-commit

From
"Nick Fankhauser"
Date:
Oh! I thought Waruna wanted to turn it off, but Björn wanted to know if it
could be controlled via psql.

Either way, both questions got answered, so we're set.

-Nick

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Thursday, September 05, 2002 3:48 PM
> To: nickf@ontko.com
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] turn off auto-commit
>
>
> Nick Fankhauser wrote:
> >
> > > Bruce Momjian wrote:
> > >
> > > Yes, autocommit appears in 7.3, due out in a few months.
> >
> > I'm confused.
> >
> > Isn't autocommit already there & on by default in psql currently?
>
> He wants to turn autocomit off:
>
>     $ psql
>     test=> UPDATE tab SET col = 1;
>     test=> UPDATE tab2 SET col = 2;
>     test=> COMMIT;
>
> With autocommit off, you basically start in a multi-statement
> transaction at the start and every time you commit.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,
> Pennsylvania 19073
>