Thread: 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.
Attachment
Is the AutoRollback checked in SQL Editor Query menu?
On Fri, Dec 18, 2015 at 10:15 PM, Frank Gard <frank.gard@exirius.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.
Dear Ashesh, thank you very much for this and your instant reply. Indeed, this was my mistake of category "layer-8" :) . Thanks a lot again and best regards, Frank. Am 18.12.2015 um 17:51 schrieb Ashesh Vashi: > 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/ > >
On Fri, Dec 18, 2015 at 10:32 PM, Frank Gard <frank.gard@exirius.de> wrote:
Dear Ashesh,
thank you very much for this and your instant reply.
Indeed, this was my mistake of category "layer-8" :) .
:)
Yes - It is a feature of pgAdmin III, as answer to your original question. :)
Thanks a lot again and best regards,
Frank.
Am 18.12.2015 um 17:51 schrieb Ashesh Vashi:
> 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/
>
>