Re: savepoint

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Marko Ristola" <marko(dot)ristola(at)kolumbus(dot)fi>
Cc: "Hiroshi Saito" <saito(at)inetrt(dot)skcapi(dot)co(dot)jp>, <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: savepoint
Date: 2005-09-04 18:20:57
Message-ID: E7F85A1B5FF8D44C8A1AF6885BC9A0E4AC9E4F@ratbert.vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi Marko,

Thanks for the analysis. Unfortunately I'm already spending far more
time on psqlODBC than I should be, so I won't be able to work on this
myself. Is it something you would like to work on? We could sure use
another programmer on the project, and you're obviously not afraid to
dive into the code :-)

Regards, Dave.

> -----Original Message-----
> From: Marko Ristola [mailto:marko(dot)ristola(at)kolumbus(dot)fi]
> Sent: 03 September 2005 14:15
> To: Dave Page
> Cc: Hiroshi Saito; pgsql-odbc(at)postgresql(dot)org
> Subject: Re: savepoint
>
>
> 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
> >
> >
>
>

Browse pgsql-odbc by date

  From Date Subject
Next Message Matthias Weinert 2005-09-05 02:21:00 last question.. sorry
Previous Message Cleber Nardelli 2005-09-04 16:44:11 Re: Transactions and SavePoints