From: | Frank Gard <frank(dot)gard(at)exirius(dot)de> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Strange behaviour concerning SAVEPOINTs |
Date: | 2015-12-18 16:45:09 |
Message-ID: | 56743815.8040905@exirius.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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 | Content-Type | Size |
---|---|---|
frank_gard.vcf | text/x-vcard | 1.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ashesh Vashi | 2015-12-18 16:51:25 | Re: Strange behaviour concerning SAVEPOINTs |
Previous Message | Владимир Янченко | 2015-12-17 08:36:32 | Re: Connection by user with restricted access to pg_database |