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

Re: [SQL] Savepoints and SELECT FOR UPDATE in 8.2

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "EXT-Rothermel, Peter M" <Peter(dot)M(dot)Rothermel(at)boeing(dot)com>
Cc: pgsql-interfaces(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Savepoints and SELECT FOR UPDATE in 8.2
Date: 2008-08-02 01:07:08
Message-ID: dcc563d10808011807l3100f873vc2e1806ddea4c642@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfacespgsql-sql
On Fri, Aug 1, 2008 at 11:02 AM, EXT-Rothermel, Peter M
<Peter(dot)M(dot)Rothermel(at)boeing(dot)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?

In response to

pgsql-sql by date

Next:From: Marcin KrawczykDate: 2008-08-04 09:53:21
Subject: return setof record - strange behavior
Previous:From: maria sDate: 2008-08-01 17:29:36
Subject: Re: column names with - and (

pgsql-interfaces by date

Next:From: Vincent PredoehlDate: 2008-08-05 01:20:38
Subject: Running a Script
Previous:From: EXT-Rothermel, Peter MDate: 2008-08-01 17:02:04
Subject: Savepoints and SELECT FOR UPDATE in 8.2

pgsql-general by date

Next:From: AndrewDate: 2008-08-02 04:08:26
Subject: Re: hibernate nativequery and uuid
Previous:From: LinosDate: 2008-08-01 22:38:25
Subject: Re: savepoint problems

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