Search Postgresql Archives

Savepoints and SELECT FOR UPDATE in 8.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux