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.
<<attachment: frank_gard.vcf>>
signature.asc
Description: OpenPGP digital signature
