Re: Strange behaviour concerning SAVEPOINTs

Hi,

using psql or a programming language API (such as Perl's DBD::Pg),
errors (EXCEPTIONs) lead to a "ROLLBACK TO" the last SAVEPOINT, if
any. Using pgAdminIII, EXCEPTIONs cause a (full) ROLLBACK, discarding
all uncommitted changes. I used the following sequence of SQL
statements and a current version of PostgreSQL (9.4) and pgAdminIII.

%<--------------------------------------------------------------------
START TRANSACTION;

-- create dummy table
DROP   TABLE IF     EXISTS tb_savepoint;
CREATE TABLE IF NOT EXISTS tb_savepoint ( id SERIAL, etwas VARCHAR );

-- correct statement
SAVEPOINT sp;
INSERT INTO tb_savepoint ( etwas ) VALUES ( 'one' ), ( 'two' );

SAVEPOINT sp;
SELECT * FROM  tb_savepoint;

-- wrong statement
SAVEPOINT sp;
SELECT ebbes FROM tb_savepoint; -- Ooooops!

ROLLBACK TO sp; -- possible only when not using pgAdminIII!
-------------------------------------------------------------------->%

Is the behaviour, different from psql (and Oracle SQLPlus, BTW),
a bug or is it a feature?

Hint: See also psql variable ON_ERROR_ROLLBACK, e.g. here:
http://blog.endpoint.com/2015/02/postgres-onerrorrollback-explained.html

Thx, Frank.