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