Postgres Pro
Downloads
Home   >   mailing lists

Thread: autocommit to off?

autocommit to off?

From
Bricklen
Date:
Hi all,

I've scoured the net for a simple way to set AUTOCOMMIT off in psql, for
the duration of a session (not simply a transaction). The only real
reference that I can find says that "\set autocommit off" will do what I
want. It doesn't though. For example, if session A inserts a row, then
session B has access to that data immediately (from another terminal).

I realize that you can fire off a begin/end pair for every statement,
but I'm looking for something more global.

Oracle's SQL*Plus is what I'm used to, which is the reason that I'm asking.

Feel free to correct any (mis)understandings I have of psql.


Cheers,

Bricklen

Re: autocommit to off?

From
"scott.marlowe"
Date:
On Wed, 3 Mar 2004, Bricklen wrote:

> Hi all,
>
> I've scoured the net for a simple way to set AUTOCOMMIT off in psql, for
> the duration of a session (not simply a transaction). The only real
> reference that I can find says that "\set autocommit off" will do what I
> want. It doesn't though. For example, if session A inserts a row, then
> session B has access to that data immediately (from another terminal).
>
> I realize that you can fire off a begin/end pair for every statement,
> but I'm looking for something more global.
>
> Oracle's SQL*Plus is what I'm used to, which is the reason that I'm asking.
>
> Feel free to correct any (mis)understandings I have of psql.

There was once a grand experiment in support autocommit = off on the
server side.  After the fires were put out, it was decided to leave it in
userland.

the standard way of "turning off" autocommit is to issue a begin; before
you do anything.  Then, should all go well you can commit; or rollback;
should things go horribly wrong.  should an error occur, Postgresql will
rollback automagically when you try to commit.

begin;
insert 1000 rows;
commit;

Re: autocommit to off?

From
Bruce Momjian
Date:
Bricklen wrote:
> Hi all,
>
> I've scoured the net for a simple way to set AUTOCOMMIT off in psql, for
> the duration of a session (not simply a transaction). The only real
> reference that I can find says that "\set autocommit off" will do what I
> want. It doesn't though. For example, if session A inserts a row, then
> session B has access to that data immediately (from another terminal).
>
> I realize that you can fire off a begin/end pair for every statement,
> but I'm looking for something more global.
>
> Oracle's SQL*Plus is what I'm used to, which is the reason that I'm asking.
>
> Feel free to correct any (mis)understandings I have of psql.

Use uppercase, \set AUTOCOMMIT off

    test=> \set AUTOCOMMIT off
    test=> select 1;
     ?column?
    ----------
            1
    (1 row)

    test=> select 2;
     ?column?
    ----------
            2
    (1 row)

    test=> commit;
    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: autocommit to off?

From
Tom Lane
Date:
Bricklen <bricklen-rem@yahoo.comz> writes:
> I've scoured the net for a simple way to set AUTOCOMMIT off in psql, for
> the duration of a session (not simply a transaction). The only real
> reference that I can find says that "\set autocommit off" will do what I
> want.  It doesn't though.

You didn't read it carefully; it said "\set AUTOCOMMIT off".  psql
variables are case-sensitive.  (Note also this requires postgres >= 7.4.)

            regards, tom lane