Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#5 SAVEPOINT needs to be done through a Connection method

open
nobody
None
5
2006-06-09
2006-06-09
Clarence
No

My application has the following outline:
1) Delete all data for a given hour
2) Do many inserts to update the data for that hour
Any of those inserts might fail due to referential
integrity violations, and that is acceptable. However,
any such error invalidates the transaction. The current
code actually does
1) Delete all data for a given hour
1a) Commit
2) Insert new data (1 tuple) for the hour
2a) Commit
2c) go to 2
This of course causes the data for that hour to be read
as either zero or any value up to the the correct
value.

So I want to use savepoints instead. But I cannot use
them through a cursor, for the same reason that one
cannot do commits/rollbacks through a cursor, namely
that the Connection object wants to know about the
state, hence it provides the commit and rollback
methods.

The Connection object needs to know about savepoints
because currently, if any query gets an error, the
Connection object issues a ROLLBACK. If the user has
previously set a savepoint via a query, the Connection
doesn't know that and the user's work is not saved.

So I would suggest some kind of savepoint access be
added to the Connection. I don't think it necessarily
needs to be as baroque as allowed by the SQL standard,
in particular multiple savepoints outstanding, etc. In
fact, I'll probably make a local modification which
will consist of just a savepoint() and release()
method, taking no arguments, just to get my problem
solved. More than that may not be necessary (given that
nobody has apparently ever even run into the problem
before).

Discussion