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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ludek Finstrle | 2006-01-23 09:06:56 | Re: Odbcapi30.c - 64 bit compiler warning cleanup |
Previous Message | zhaoxin | 2006-01-23 03:27:06 | Re: some question about SavePoint ? |