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

Re: savepoint

From: "Hiroshi Saito" <saito(at)inetrt(dot)skcapi(dot)co(dot)jp>
To: "Marko Ristola" <marko(dot)ristola(at)kolumbus(dot)fi>,"Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: savepoint
Date: 2005-09-06 09:01:31
Message-ID: 048501c5b2c1$92d17540$01324d80@hiroshi5jz7dqj (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi Marko.

> BEGIN
> Stmt 1: SAVEPOINT svp
> Stmt 1: CREATE CURSOR CC AS SELECT values from TEST LIMIT 2 -- OK
> Stmt 1: FETCH 1 -- fetched one row
> Stmt 2: INSERT INTO VALUES -- ERROR.
> Stmt 2: ROLLBACK TO svp:
>    Inside PSQLODBC:
>    1. PSQLDODBC calls CLOSE CURSOR CC. Error, because transaction is in 
> ERROR condition.
>    2. ROLLBACK TO svp: this won't be done, because (1) failed already.
>    Correct way here is to NOT CLOSE DEAD CURSORS!
> Stmt 1: FETCH 1 -- fetched one row -- NOT DONE
> CLOSE CURSOR CC -- NOT DONE
> END

Umm, Actually, the cursor name will not be found when it ROLLBACK out of cursor.
It is a phenomenon which happens even if it is not psqlodbc....
I consider the following scenario.

CREATE TABLE svtest (k1 varchar(19) PRIMARY KEY,d1 varchar(10));
INSERT INTO svtest VALUES('X','1');
INSERT INTO svtest VALUES('Y','2');
INSERT INTO svtest VALUES('Z','3');
BEGIN;
SAVEPOINT svp1;
DECLARE CC CURSOR FOR SELECT * from svtest LIMIT 2;
FETCH FIRST FROM CC;
SAVEPOINT svp2;
INSERT INTO svtest values('X','99');
ROLLBACK TO svp2;
INSERT INTO svtest values('A','0');
FETCH FIRST FROM CC;
FETCH NEXT FROM CC;
CLOSE CC;
END;
SELECT * FROM svtest;
DROP TABLE svtest CASCADE;

The above works with psqlodbc. However, Updating cursor is not considered carefully. 

Regards,
Hiroshi Saito


In response to

Responses

pgsql-odbc by date

Next:From: zuschlag2Date: 2005-09-06 11:46:51
Subject: Re: Continuing encoding fun....
Previous:From: Anoop KumarDate: 2005-09-06 08:53:16
Subject: Re: Continuing encoding fun....

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