Can't set AUTOCOMMIT?

[Josh]

Hi there,

I'm having trouble turning on auto commit in vsql. Can someone please take a look at the following and let me know what I'm doing wrong?

Code: Select all

dbadmin=> show autocommit;
    name    | setting
------------+---------
 autocommit | off
(1 row)

dbadmin=> \set autocommit = 'on';
dbadmin=> show autocommit;
    name    | setting
------------+---------
 autocommit | off
(1 row)

dbadmin=> \set AUTOCOMMIT = 'on';
dbadmin=> show autocommit;
    name    | setting
------------+---------
 autocommit | off
(1 row)

[jpcavanaugh]

Can you try:

\set AUTOCOMMIT on

[Josh]

Interesting... The show AUTOCOMMIT command always shows as off while the \set command by itself does show change to the variable AUTOCOMMIT... So what it the purpose of show AUTOCOMMIT?

Code: Select all

dbadmin=> \set AUTOCOMMIT on
dbadmin=> show AUTOCOMMIT;
    name    | setting
------------+---------
 autocommit | off
(1 row)

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
LASTOID = '0'
HISTSIZE = '500'

dbadmin=> \set AUTOCOMMIT off
dbadmin=> show AUTOCOMMIT;
    name    | setting
------------+---------
 autocommit | off
(1 row)

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
LASTOID = '0'
HISTSIZE = '500'

[Josh]

I'm confused by my testing shown below. There seems to be two ways to turn on and off auto commit in vsql.

1. Using the internal variable AUTOCOMMIT (the one we set with the \pset meta-command)
2. Setting the autocommit variable (the one we set with the set autocommit command)

But I'm not sure which one take precedence....

Test #1 (\set AUTOCOMMIT):

In Session 1:

Code: Select all

dbadmin=> \set AUTOCOMMIT off;
dbadmin=> create table test (col1 int);
CREATE TABLE

dbadmin=> insert into test values (1);
 OUTPUT
--------
      1
(1 row)

In Session 2; No Data Committed from Session 1 because AUTOCOMMIT is off:

Code: Select all

dbadmin=> select * from test;
 col1
------
(0 rows)

Back to Session 1:

Code: Select all

dbadmin=> \set AUTOCOMMIT on

In Session 2 we see that the INSERT in session 1 was committed just by running the \set AUTOCOMMIT on statement:

Code: Select all

dbadmin=> select * from test;
 col1
------
    1
(1 row)

Test #2 (set autocommit):

In Session 1:

Code: Select all

dbadmin=> create table test (col1 int);
CREATE TABLE
dbadmin=> insert into test values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> show autocommit;
    name    | setting
------------+---------
 autocommit | off
(1 row)

In Session 2:

Code: Select all

dbadmin=> select * from test;
 col1
------
(0 rows)

Back to Session 1:

Code: Select all

dbadmin=> set autocommit = on;
SET

Back to Session 2; the INSERT statement from Session 1 was committed!

Code: Select all

dbadmin=> select * from test;
 col1
------
    1
(1 row)

Weird! I can turn one off while the other is on and vice versa!

Code: Select all

dbadmin=> set autocommit = on;
SET
dbadmin=> \set AUTOCOMMIT off
dbadmin=> show autocommit;
    name    | setting
------------+---------
 autocommit | on
(1 row)

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
LASTOID = '0'
HISTSIZE = '500'
dbadmin=>

dbadmin=> \set AUTOCOMMIT on
dbadmin=> set autocommit = off;
SET
dbadmin=> show autocommit;
    name    | setting
------------+---------
 autocommit | off
(1 row)

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
LASTOID = '0'
HISTSIZE = '500'