I run multiple SQL queries from pgadmin such as
INSERT INTO db1 (name)
VALUES ('Joe');
INSERT INTO db1 (name)
VALUES ('Bob');
in a single query window. It runs fine and I see both names in my table. However, they are part of the same transaction. Does pgadmin automatically add a BEGIN and COMMIT before and after my queries? Thanks Murthy
To illustrate what I'm talking about:
--In psql
create table autocommit_test(id integer, fld_1 varchar);
\echo :AUTOCOMMIT
on
insert into autocommit_test values(1, 'one'); insert into autocommit_test values(2, 'two');
INSERT 0 1
INSERT 0 1
insert into autocommit_test values(3, 'three'); insert into autocommit_test values('four', 'four');
INSERT 0 1
ERROR: invalid input syntax for type integer: "four"
LINE 1: insert into autocommit_test values('four', 'four');
select xmin, * from autocommit_test ;
xmin | id | fld_1
--------+----+-------
400129 | 1 | one
400130 | 2 | two
400131 | 3 | three
\set AUTOCOMMIT off
\echo :AUTOCOMMIT
off
insert into autocommit_test values(4, 'four'); insert into autocommit_test values(5, 'five');
INSERT 0 1
INSERT 0 1
select xmin, * from autocommit_test ;
xmin | id | fld_1
--------+----+-------
400129 | 1 | one
400130 | 2 | two
400131 | 3 | three
400132 | 4 | four
400132 | 5 | five
--From another psql session
select xmin, * from autocommit_test ;
xmin | id | fld_1
--------+----+-------
400129 | 1 | one
400130 | 2 | two
400131 | 3 | three
--Commit in insert session
commit;
--Other session
select xmin, * from autocommit_test ;
xmin | id | fld_1
--------+----+-------
400129 | 1 | one
400130 | 2 | two
400131 | 3 | three
400132 | 4 | four
400132 | 5 | five