Thread: Re: savepoint

> -----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

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
>
>

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

Hiroshi Saito wrote:

>
>The above works with psqlodbc. However, Updating cursor is not considered carefully.
>
>Regards,
>Hiroshi Saito
>
>
>
Thats good that it works with your scenario.
My own feeling about my example was, that the exact case I described
wasn't very good.

I did find the cursor problem during this summer, when I played with
savepoints.
That was before the libpq was put into the CVS.
I tried then hard to understand the whole problem, not just some cases
of it.
I tried to make a robust implementation.

The problem case was and is as follows:
1. An ODBC statement handle holds an internal cursor.
2. That cursor has been rolled back by the backend via calling ROLLBACK
TO SAVEPOINT
    and the transaction is under READY state.
    Or then the transaction is under FAILED state, and the backend has
removed the cursor, or
    will remove the cursor during ROLLBACK TO SAVEPOINT.
3. Statement closing tries to close the nonexistent cursor.
This causes the current transaction to go into the FAILED state, if it
is not already in that state,
thus making things possibly worse.

This occurred to me, when I was playing with UseDeclareFetch=1 and with
savepoint testing.
There the ODBC did try to roll back the nonexistent cursor. Maybe the
transaction
was in the failed state at that point.

As you already might have read my previous email, UseDeclareFetch=1
doesn't work
with the current libpq ODBC driver. So I can't test it's behaviour now.

Here is one scenario with UseDeclareFetch=1 and Fetch=2, but I don't
know  wether it works,
because I can't test it. The backend fetches at most 2 rows per fetch.

NEW_STMT_HANDLE=SELECT * from test1 LIMIT 20000000;
foreach row in CC ; do
    SAVEPOINT S1;
    INSERT each fetched row INTO TABLE test2;
    RELEASE S1 (or ROLLBACK TO S1 on failure).
done
DROP_STMT

With the above pseudo code, psqlodbc FETCHes twenty million rows
from the backend with two rows per internal PSQLODBC fetch. A low
memory consumption can be garanteed on the client side with 32 bit
computers.

Unfortunately PostgreSQL uses here indexes, because it assumes, that using
cursors means that only a fraction will be fetched.
Without UseDeclareFetch, the program would crash.

Conclusion:
These problem scenarios occur only with ODBC statement handle maintained
cursors.
Do you know, wether there any other such uses, than the UseDeclareFetch=1?

If the internal cursor is created and closed within the one single ODBC
call, this problem
scenario doesn't occur, or it can be fixed with easy coding.

When the ODBC using program has the responsibility on tracking and
closing the open CURSORs,
the savepoints are able to work nicely without the savepoint
maintenance code inside psqlodbc. The application programmer knows,
when it's cursors exist, and when not.

So maybe I have kept this problem too big, and tried to solve it
with too much work after all.

Regards,
Marko