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

Re: savepoint

From: Marko Ristola <marko(dot)ristola(at)kolumbus(dot)fi>
To: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
Cc: Hiroshi Saito <saito(at)inetrt(dot)skcapi(dot)co(dot)jp>,pgsql-odbc(at)postgresql(dot)org
Subject: Re: savepoint
Date: 2005-09-03 13:14:54
Message-ID: 4319A1CE.4010007@kolumbus.fi (view raw or flat)
Thread:
Lists: pgsql-odbc
Here is a harder scenario (with pseudo SQL):

BEGIN
SAVEPOINT svp
CREATE CURSOR CC AS SELECT values from TEST LIMIT 2 -- OK
FETCH 1 -- fetched one row
FETCH 1 -- fetched one row
CLOSE CURSOR CC
ROLLBACK TO svp
END

The above works with psqlodbc.

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

Unfortunately the PostgreSQL backend does
not report the closing of the cursor CC with a NOTIFY message.

So the full implementation requires tracking of the
cursor liveness within savepoints.

Another way is to adjust the future PostgreSQL 8.1 to notify the 
PsqlODBC about
each cursor that gets closed by the backend. That way, the Psqlodbc 
CC_send_query()
can be adjusted to react into those NOTIFY
messages. Then the fix would be to react into those NOTIFY messages, and 
the PsqlODBC
driver would traverse the stmt list and close each notified cursor 
silently. The programmer should
not close them explicitely. Programmer's cursor closing would not harm 
though, because
it is already closed.

Third and worst of the easy problem solutions is that the PsqlODBC user 
program
would inform the Statement handle so, that the CLOSE CURSOR CC will not 
be delivered
for the PostgreSQL server. Psqlodbc programmer could learn the skills to 
know
within his program, which cursors are dead. Maybe something like 
SQLSetStmtAttr(stmt2,CURSOR_IS_DEAD_ON_THE_BACKEND);
could do something like

// this is just the trivial fix, that unbinds the CURSOR,
// not good C coding inside PsqlODBC:
if (stmt->cursor != NULL) stmt->cursor[0]='\0';

Of course, this might mean good bye for ODBC conformance and hard times for
psqlodbc savepoint users: It is easier for us to solve the logical 
problem once
and give an easy programming environment for the users.


Maybe the best implementation is that the PostgreSQL Server
does not close the cursors _silently_ -- closes them, but with a NOTIFY 
message.

Then we adjust the CC_send_query() accordingly to clean up the Stmt's 
internal
structures of an already closed cursor.

Then we could implement the SAVEPOINT support for PostgreSQL 8.1.
And we would not need to create a duplicate of the CURSOR behaviour
in SAVEPOINTs

Regards,
Marko Ristola

Dave Page wrote:

> 
>
>  
>
>>-----Original Message-----
>>From: Hiroshi Saito [mailto:saito(at)inetrt(dot)skcapi(dot)co(dot)jp] 
>>Sent: 02 September 2005 03:53
>>To: Hiroshi Saito; Dave Page
>>Cc: Marko Ristola
>>Subject: Re: savepoint
>>
>>Uga.. 
>>sorry && missing..
>>replay this.
>>
>>    
>>
>>>Hi Dave. and Dear Marko.
>>>
>>>I consider many things about the importance of savepoint.
>>>Probably, this will be important for the programmer of 
>>>operating application. Marko has suggested this thoughtfully.
>>>This is supported by the reason I also have a realistic idea.
>>>Though it is the pretreatment which carries out honest 
>>>correspondence to the future, I think that this is good.
>>>
>>>Would you consider application? Or another suggestion.
>>>      
>>>
>
>
>Hi Hiroshi
>
>Please post to the list!!
>
>I've updated the patch as attached:
>
>- It's a little more readable I think
>- It works with libpq as well as the old comms :-)
>- It should be case insensitive (otherwise, you could just use
>strstr()).
>
>There's also some minor reformatting of misc.h in there.
>
>It is applied to CVS - thanks Hiroshi.
>
>Regards, Dave
>  
>


In response to

Responses

pgsql-odbc by date

Next:From: Dave PageDate: 2005-09-03 19:47:38
Subject: Continuing encoding fun....
Previous:From: Johann ZuschlagDate: 2005-09-03 11:34:32
Subject: Re: Unicode support, first test results

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