DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=tfGo2v/cw+bnLwPZeJ/8mhixG7MYj7BogrfdYooKiLTmj7k2jYPo/IX2Vw7bgA4Jop
	9T5WFlH3ItaPxa48zagPO4Vh9lyy3d24LQRNmKHdkHjJeoM8VU8Zv7CNjNNMlAd50VgQ
	PUWFo32Woc/EiBqVX1A20MLi3ElauqzeWcka0=
Message-ID: <dcc563d10808011807l3100f873vc2e1806ddea4c642@mail.gmail.com>
Date: Fri, 1 Aug 2008 19:07:08 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "EXT-Rothermel, Peter M" <Peter.M.Rothermel@boeing.com>
Subject: Re: [SQL] Savepoints and SELECT FOR UPDATE in 8.2
Cc: pgsql-interfaces@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
In-Reply-To: 
 <8D9E4E8445BD14478121CC9B027B518AB57AA0@XCH-NW-11V2.nw.nos.boeing.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: 
 <8D9E4E8445BD14478121CC9B027B518AB57AA0@XCH-NW-11V2.nw.nos.boeing.com>

On Fri, Aug 1, 2008 at 11:02 AM, EXT-Rothermel, Peter M
<Peter.M.Rothermel@boeing.com> wrote:
>
> 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?

Why not plain rollback?