Skip site navigation (1) Skip section navigation (2)

Transaction Questions

From: Richard Kut <rkut(at)intelerad(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Mathew Kanner <Mathew(dot)Kanner(at)intelerad(dot)com>
Subject: Transaction Questions
Date: 2006-02-24 14:57:06
Message-ID: 200602240957.06798.rkut@intelerad.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello!

	I have observed that there is no way to keep a SAVEPOINT inside a BEGIN; ... 
END; block after an error condition. I created the following table for 
testing:

xyz=> \d t1
           Table "public.t1"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 c1     | character varying |
 n1     | integer           |
Indexes:
    "t1_c1_idx" UNIQUE, btree (c1)

xyz=>

I then tried the following:

BEGIN;
  INSERT INTO t1 VALUES ('w', 1);
  SAVEPOINT p1;
  INSERT INTO t1 VALUES ('x', 2);
  ROLLBACK TO SAVEPOINT p1;
END;

and that works as expected. However, if I get an error within the transaction 
anywhere after the SAVEPOINT command, then the entire transaction fails, and 
any changes made prior to the SAVEPOINT are rolled back. For example:

xyz=> TRUNCATE TABLE t1;
TRUNCATE TABLE
xyz=> BEGIN;
BEGIN
xyz=> INSERT INTO t1 VALUES ('w', 1);
INSERT 0 1
xyz=> SAVEPOINT p1;
SAVEPOINT
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
 w  |  1
(1 row)

xyz=> INSERT INTO t1 VALUES ('x', 2);
INSERT 0 1
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
 w  |  1
 x  |  2
(2 rows)

xyz=> SELECT 1 / 0;
ERROR:  division by zero
xyz=> SELECT * FROM t1;
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
xyz=> END;
ROLLBACK
xyz=> SELECT * FROM t1;
 c1 | n1
----+----
(0 rows)

xyz=>

	Is this the way that it is meant to work, or is there something wrong in my 
approach? Is it possible to have some statements within a transaction succeed 
and be committed to the database while others fail and are rolled back?

-- 
Regards,

Richard Kut
Database Administrator
Research & Development
Intelerad Medical Systems Inc.
460 Ste-Catherine West, Suite 210
Montreal, Quebec, Canada H3B 1A7
Tel:     514.931.6222 x7733
Fax:     514.931.4653
rkut(at)intelerad(dot)com
www.intelerad.com

This email or any attachments may contain confidential or legally
privileged information intended for the sole use of the addressees. Any
use, redistribution, disclosure, or reproduction of this information,
except as intended, is prohibited. If you received this
email in error, please notify the sender and remove all copies of the
message, including any attachments.

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2006-02-24 16:14:56
Subject: Re: Transaction Questions
Previous:From: Bruno Wolff IIIDate: 2006-02-24 14:38:50
Subject: Re: check problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group