> 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
