Skip site navigation (1) Skip section navigation (2)

Re: some question about SavePoint ?

From: Ludek Finstrle <luf(at)pzkagis(dot)cz>
To: zhaoxin <zhaox(at)necas(dot)nec(dot)com(dot)cn>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: some question about SavePoint ?
Date: 2006-01-23 09:01:39
Message-ID: 20060123090139.GA20685@soptik.pzkagis.cz (view raw or flat)
Thread:
Lists: pgsql-odbc
> I want to do like this:
>  1. set autocommit = off ;
>  2. for(i=0;i<len;i++){
>      savepoint sp;
>      insert into test(c1,c2) values(i,'a');
>      release savepoint sp;
>      if dupkey error occur then
>         rollback to sp;

You can't rollback to released savepoint.

>  	update test set c2 = 'b';
>      else{
>         rollback and return;
>      }
>   }
>   3.commit;

This may work. But the performance is low.

1. set autocommit = off ;
2. for(i=0;i<len;i++){
    savepoint sp;
    insert into test(c1,c2) values(i,'a');
    if dupkey error occur then
       rollback to sp;
       release savepoint sp;
       update test set c2 = 'b';
    else{
       rollback and return;
    }
 }
 3.commit;

> Can I use another way to implenment this ? (don't use the savepoint)

If you use autocommit = off and test each line separately I see no
other way. It's the backend behaviour when something fail inside
manual transaction. You could try it in psql.

I suppose c1 or (ci, c2) is primary or unique key.
What about something like (I don't fully understand what you
really want):

1. set autocommit = off;
2. update test set c2='b' where c1 between <min> and <max> and c2 = 'a';
3. if (<update count> < (<max> - <min>)) then
     rollback;
   else
     commit;

<min> = 0 from your example
<max> = len from your example
<update count> = number of updated rows

Next way could be:
1. set autocommit = off;
2. select c1,c2 from test where c1 between <min> and <max> ...
3. if (check the data) then
     update or for(...) update
     commit;
   else
     rollback;

I'm not sure if this really remain into pgsql-odbc mailing list. There
are maybe more suitable people in pgsql-sql or another pgsql-* mailing
list.

Regards,

Luf

In response to

pgsql-odbc by date

Next:From: Ludek FinstrleDate: 2006-01-23 09:06:56
Subject: Re: Odbcapi30.c - 64 bit compiler warning cleanup
Previous:From: zhaoxinDate: 2006-01-23 03:27:06
Subject: Re: some question about SavePoint ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group