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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-interfaces pgsql-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

Browse pgsql-general by date

  From Date Subject
Next Message Nathan Thatcher 2008-08-01 17:55:02 GROUP BY hour
Previous Message Martijn van Oosterhout 2008-08-01 17:01:32 Re: savepoint problems

Browse pgsql-interfaces by date

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

Browse pgsql-sql by date

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