Обсуждение: Transactions and SavePoints

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

--
Cleber Nardelli

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

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.ristola@kolumbus.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

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.ristola@kolumbus.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
>>
>>
>>
>
>
>
>

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.Ristola@kolumbus.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.ristola@kolumbus.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

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.Ristola@kolumbus.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.ristola@kolumbus.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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>

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.Ristola@kolumbus.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.Ristola@kolumbus.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.ristola@kolumbus.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