pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedSavepoints and SELECT FOR UPDATE in 8.2
2+ messages / 2 participants
[nested] [flat]
* Savepoints and SELECT FOR UPDATE in 8.2
@ 2008-08-01 17:02 EXT-Rothermel, Peter M <Peter.M.Rothermel@boeing.com>
0 siblings, 1 reply; 2+ messages in thread
From: EXT-Rothermel, Peter M @ 2008-08-01 17:02 UTC (permalink / raw)
To: pgsql-interfaces; pgsql-general@postgresql.org; +Cc: pgsql-sql@postgresql.org
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Savepoints and SELECT FOR UPDATE in 8.2
@ 2008-08-02 01:07 Scott Marlowe <scott.marlowe@gmail.com>
parent: EXT-Rothermel, Peter M <Peter.M.Rothermel@boeing.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2008-08-02 01:07 UTC (permalink / raw)
To: EXT-Rothermel, Peter M <Peter.M.Rothermel@boeing.com>; +Cc: pgsql-interfaces; pgsql-general@postgresql.org; pgsql-sql@postgresql.org
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-08-02 01:07 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-01 17:02 Savepoints and SELECT FOR UPDATE in 8.2 EXT-Rothermel, Peter M <Peter.M.Rothermel@boeing.com>
2008-08-02 01:07 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox