Re: Transaction Questions

On 2/24/06 3:14 PM, "Richard Kut" <rkut(at)intelerad(dot)com> wrote:

> Hi Sean!
>
> I tried it, but it did not help. Here is the output:
>
> xyz=> TRUNCATE TABLE t1;
> TRUNCATE TABLE
> xyz=> BEGIN;
> BEGIN
> xyz=> SAVEPOINT p1;
> SAVEPOINT
> xyz=> INSERT INTO t1 VALUES ('w', 1);
> INSERT 0 1
> xyz=> RELEASE SAVEPOINT p1;
> RELEASE
> xyz=> SELECT * FROM t1;
> c1 | n1
> ----+----
> w | 1
> (1 row)
>
> xyz=> SAVEPOINT p2;
> SAVEPOINT
> xyz=> INSERT INTO t1 VALUES ('w', 1);
> ERROR: duplicate key violates unique constraint "t1_c1_idx"
> xyz=> RELEASE SAVEPOINT p2;
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
> xyz=> END;
> ROLLBACK

Sorry. Spoke too quickly. Try "rollback to savepoint"

create table table1 (
c1 varchar primary key,
n1 int
);
psql: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"table1_pkey" for table "table1"
CREATE TABLE
BEGIN;
BEGIN
SAVEPOINT p1;
SAVEPOINT
INSERT INTO TABLE1 VALUES ('w',1);
INSERT 0 1
RELEASE SAVEPOINT p1;
RELEASE
SAVEPOINT p2;
SAVEPOINT
INSERT INTO TABLE1 VALUES ('w',2);
psql:13: ERROR: duplicate key violates unique constraint "table1_pkey"
ROLLBACK TO SAVEPOINT p2;
ROLLBACK
END;
COMMIT
select * from table1;
c1 | n1
----+----
w | 1