Re: Strange behaviour concerning SAVEPOINTs

Is the AutoRollback checked in SQL Editor Query menu?

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
<http://www.enterprisedb.com>

*http://www.linkedin.com/in/asheshvashi*
<http://www.linkedin.com/in/asheshvashi>

On Fri, Dec 18, 2015 at 10:15 PM, Frank Gard <frank(dot)gard(at)exirius(dot)de> wrote:

> 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.
>