Skip site navigation (1) Skip section navigation (2)

Savepoints and SELECT FOR UPDATE in 8.2

From: "EXT-Rothermel, Peter M" <Peter(dot)M(dot)Rothermel(at)boeing(dot)com>
To: <pgsql-interfaces(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Savepoints and SELECT FOR UPDATE in 8.2
Date: 2008-08-01 17:02:04
Message-ID: 8D9E4E8445BD14478121CC9B027B518AB57AA0@XCH-NW-11V2.nw.nos.boeing.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfacespgsql-sql
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?




Responses

pgsql-sql by date

Next:From: maria sDate: 2008-08-01 17:29:36
Subject: Re: column names with - and (
Previous:From: Marcin KrawczykDate: 2008-08-01 12:25:32
Subject: Re: regexp_replace

pgsql-interfaces by date

Next:From: Scott MarloweDate: 2008-08-02 01:07:08
Subject: Re: [SQL] Savepoints and SELECT FOR UPDATE in 8.2
Previous:From: Francisco Figueiredo Jr.Date: 2008-07-29 22:02:09
Subject: [ANN] Npgsql2 RC1 Released!!

pgsql-general by date

Next:From: Nathan ThatcherDate: 2008-08-01 17:55:02
Subject: GROUP BY hour
Previous:From: Martijn van OosterhoutDate: 2008-08-01 17:01:32
Subject: Re: savepoint problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group