I have a client application that needs:
SELECT a set of records from a table and lock them for potential
updates.
for each record
make some updates to this record and some other records in other
tables
call some call a function that does some application logic that
does not access the database
if this function is successful
commit the changes for this record
release any locks on this record
if the function fails
rollback any changes for this record
release any locks for this record
It would not be too much of a problem if the locks for all the records
were held until all these
records were processed. It would probably not be too bad if all the
changes were not committed
until all the records were processed. It is important that all the
records are processed even when
some of iterations encounter errors.
I was thinking of something like this:
connect to DB
BEGIN
SELECT * FROM table_foo where foo_state = 'queued' FOR UPDATE;
for each row
do [
SAVEPOINT s;
UPDATE foo_resource SET in_use = 1 WHERE ...;
status = application_logic_code(foo_column1, foo_column2);
IF status OK
THEN
ROLLBACK TO SAVEPOINT s;
ELSE
RELEASE SAVEPOINT s;
ENDIF
]
COMMIT;
I found a caution in the documentation that says that SELECT FOR UPDATE
and SAVEPOINTS is not implemented correctly in version 8.2:
http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-U
PDATE-SHARE
Any suggestions?
