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

select for update question

From: A B <gentosaker(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: select for update question
Date: 2010-01-12 23:34:13
Message-ID: dbbf25901001121534m33d21c26s9062c56296774fe7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello there.
I'm looking into how to update a row in a table while protecting it
from access by others.

so far I've come up with this solution:

create function dostuff() returns void as $$
declare
  tmp integer;
begin
  select id into tmp from tableX where id>305 limit 1 for update;
  update tableX set some_field = some_value where id=tmp;
end; $$ language plpgsql;

will that guarantee that  the row I selected wil be updated within
this function and no one else can sneak in between and update or
delete the row?


What would I use if I would write

lock table   tableX  IN .... MODE  at the start of my function?

Any particular benefit with either method?

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-01-13 06:05:46
Subject: Re: select for update question
Previous:From: e-letterDate: 2010-01-11 19:59:32
Subject: connection to database via xforms

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