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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql