Re: Transactions and SavePoints

Hi....

I find that I am not accustomed with this business of CVS.

I do not understand inside.... of pgfoundry I exist the option of if
to see the tree of the CVS (historical), I obtain to see the
modifications made in the archives, but the option of compilation of
the current version I did not find.

It will be that you it could also give an aid to me in this...

Thank you...
Cleber.

2005/9/3, Marko Ristola <Marko(dot)Ristola(at)kolumbus(dot)fi>:
>
> Hi.
>
> Unfortunately I don't have a Windows compilation environment for
> building the driver either.
>
> Hiroshi Saito and Dave Page made a good fix based on my solution
> below. The fix is in CVS now.
>
> Maybe somebody else on this list can compile the latest version
> of the development driver or the stable driver with this fix
> with the patch for you.
>
> Regards,
> Marko Ristola
>
> Cleber Nardelli wrote:
>
> >Hi...
> >Marko, debtor for the aid.
> >Not wanting to ask for very, but it would like to lher to ask for of
> >the same skill..... I already I had given one looked at in the sources
> >of driver and more or less had identified the problem, ok, but it
> >happens that I I do not have much knowledge in C and I do not obtain
> >to compile driver, will be that you it could not make this for me?
> >
> >In any obliged way
> >
> >Cleber
> >
> >2005/9/1, Marko Ristola <Marko(dot)Ristola(at)kolumbus(dot)fi>:
> >
> >
> >>Unfortunately SAVEPOINT support has not been implemented
> >>into psqlodbc driver yet.
> >>
> >>So it doesn't work unfortunately.
> >>
> >>However, if you have a possibility use the native PostgreSQL
> >>libpq driver (not an ODBC driver) instead,
> >>you can use fully featured savepoints.
> >>
> >>Depending on your needs, you can even pay
> >>somebody to implement the feature into psqlodbc. A good enough
> >>implementation could be included into the driver.
> >>
> >>I have a small part of an implementation, but that's not enough.
> >>
> >>You described your problem: the ODBC driver does the rollback
> >>for you, even though you don't want it.
> >>
> >>The problem is just the ice berg of the lack of the
> >>SAVEPOINT feature in the psqlodbc driver.
> >>
> >>
> >>However, I know one easy solution into the problem, you found:
> >>
> >>Get source code of the psqlodbc ODBC driver.
> >>
> >>On file connection.c, C code function CC_send_query():
> >>
> >> else if (strnicmp(cmdbuffer, "ROLLBACK", 8) == 0)
> >> CC_on_abort(self, NO_TRANS);
> >>
> >>The above must be changed not to call CC_on_abort(self,NO_TRANS), if the
> >>case
> >>is "ROLLBACK TO SAVEPOINT name";
> >>CC_on_abort(self,NO_TRANS); must be called on "ROLLBACK WORK"
> >>
> >>NO_TRANS means, that the ODBC thinks, that there is no transaction in the
> >>PostgreSQL server.
> >>
> >>So you can get your problem away with:
> >>
> >> else if (strnicmp(cmdbuffer, "ROLLBACK", 8) == 0 &&
> >>(strnicmp(query,"ROLLBACK TO",11) != 0)
> >> CC_on_abort(self, NO_TRANS);
> >>
> >>I used "query" argument, because cmdbuffer is "ROLLBACK", but query is
> >>"ROLLBACK TO SAVEPOINT name".
> >>
> >>Then you need to recompile the ODBC driver, and you don't have that
> >>problem anymore.
> >>ODBC debugging options help you to track further problems with savepoints.
> >>
> >>With this, only a partial SAVEPOINT implementation can be achieved.
> >>You might get well with SAVEPOINTs, if you don't use CURSORs,
> >>because with them the problem becomes a lot harder, and the simple
> >>fix from above won't work anymore.
> >>
> >>Yours,
> >>Marko Ristola
> >>
> >>Cleber Nardelli wrote:
> >>
> >>
> >>
> >>>Marko, debtor for the aid that you have given me.
> >>>
> >>>My problem in the truth nor is SAVEPOINT and ROLLBACK You the SAVEPOINT.
> >>>
> >>>My problem is that when an error is generated during the execution of
> >>>commands inside of a transaction driver automatically it makes a
> >>>ROLLBACK of all the executed commands until then and ai I do not
> >>>obtain to execute the ROLLBACK You the SAVEPOINT therefore the block
> >>>of the transaction already does not exist more.
> >>>
> >>>Yours truly Cleber
> >>>
> >>>2005/8/31, Marko Ristola <marko(dot)ristola(at)kolumbus(dot)fi>:
> >>>
> >>>
> >>>
> >>>
> >>>>Hi
> >>>>
> >>>>I tried to write SAVEPOINT SQL support into PsqlODBC in Summer.
> >>>>
> >>>>I was able to implement some suport for savepoint creation, savepoint
> >>>>rollback and savepoint RELEASE.
> >>>>
> >>>>I didn't understand, how the query results should behave at savepoint
> >>>>rollbacks.
> >>>>
> >>>>You can find more information about my thoughts about savepoint
> >>>>implementation on this list ealier this year, or with Google.
> >>>>
> >>>>Main design issue was, that SAVEPOINT tracking must be implemented
> >>>>into PSQLODBC.
> >>>>
> >>>>That's what I implemented:
> >>>>- Linked list of all defined savepoints.
> >>>>- Maintain the list during SAVEPOINT maintenance functions.
> >>>>
> >>>>Savepoint command parsing speed was 600000 commands per second,
> >>>>that I wrote (UTF-8). With UCS-4 , without a state machine, it was
> >>>>300000 commands per second. The subsystem's speed was okay.
> >>>>
> >>>>I implemented a full PostgreSQL SQL SAVEPOINT support with
> >>>>the Bison/Flex parser, because it is easy, but that part is
> >>>>not at all easy to integrate into an existing PsqlODBC driver.
> >>>>
> >>>>Are you interested in a patch for the mergeable part of the savepoint
> >>>>support?
> >>>>
> >>>>Regards,
> >>>>Marko Ristola
> >>>>
> >>>>Carlos Henrique Reimer wrote:
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>Is there a ODBC flavor than can implement ROLLBACK TO SAVEPOINT?
> >>>>>
> >>>>>Marko Ristola wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Unfortunately SAVEPOINTs are not implemented in PsqlODBC yet.
> >>>>>>
> >>>>>>
> >>>>>>ROLLBACK TO SAVEPOINT -case is easy to fix, but the full
> >>>>>>implementation is hard to make correctly for psqlodbc, at least for me.
> >>>>>>
> >>>>>>Marko Ristola
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>Cleber Nardelli wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>Hello.
> >>>>>>>I am using the Driver odbc for access to postgresql. I have a
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>referring problem to the cancellation of the transaction when
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>>some error
> >>>>>>>is found at the moment of the execution of the same one. I try to
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>execute this sql command:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>>BEGIN TRANSACTION;
> >>>>>>>DELETE FROM TABL1 WHERE ID = 1;
> >>>>>>>INSERT INTO TABL1 VALUES (1,1);
> >>>>>>>SAVEPOINT SVPT;
> >>>>>>>INSERT INTO TABL1 VALUES (1,1);
> >>>>>>>ROLLBACK TO SAVEPOINT SPVT;
> >>>>>>>COMMIT;
> >>>>>>>
> >>>>>>>When I try to insert as register it accuses to error with key
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>duplicate. Ok. But I do not want to cancel the transaction I I need to
> >>>>>only continue
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>>with the same one skirting this error. For this reason I am using
> >>>>>>>SavePoints but it generates the error:
> >>>>>>>"ERROR: ROLLBACK TO SAVEPOINT may only be used in transaction blocks"
> >>>>>>>
> >>>>>>>Necessary of the your help. I thank
> >>>>>>>
> >>>>>>>Yours truly
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>------------------------------------------------------------------------
> >>>>>Yahoo! Acesso Grátis
> >>>>><%20http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/>:
> >>>>>Internet rápida e grátis. Instale o discador agora!
> >>>>><%20http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>---------------------------(end of broadcast)---------------------------
> >>>>TIP 3: Have you checked our extensive FAQ?
> >>>>
> >>>> http://www.postgresql.org/docs/faq
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
> >
>
>

--
Cleber Nardelli
IPM Automação e Consultoria
9998-8670