Re: savepoint - Mailing list 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@inetrt.skcapi.co.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
>
>