agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedselect [for update]??
17+ messages / 15 participants
[nested] [flat]
* select [for update]??
@ 1999-08-15 05:51 Matthew Hagerty <matthew@venux.net>
0 siblings, 0 replies; 17+ messages in thread
From: Matthew Hagerty @ 1999-08-15 05:51 UTC (permalink / raw)
To: pgsql-sql
Greetings,
Can someone explain to me how to use a "select for update"?
Thanks,
Matthew
^ permalink raw reply [nested|flat] 17+ messages in thread
* select ... for update
@ 2000-12-12 22:03 Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jie Liang @ 2000-12-12 22:03 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
> Hi,
How can I use select ... for update to update limit to update what I
select??
somewhat like:
select url,id from mytable for update order by priority,id limit 5;
I want update the id in above return like:
update mytable set allocatedto='whatever' where id in above return set.
Could I do it in one stmt.
And what is class_name in following:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ AS name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
[ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF class_name [, ...] ] ]
LIMIT { count | ALL } [ { OFFSET | , } start ]
can any one give me a example??
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: select ... for update
@ 2000-12-13 08:28 Karel Zak <zakkr@zf.jcu.cz>
parent: Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 17+ messages in thread
From: Karel Zak @ 2000-12-13 08:28 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql
> > Hi,
>
> How can I use select ... for update to update limit to update what I
> select??
First thing - the SELECT FOR UPDATE is not merge of SELECT and UPDATE
but transaction option. The PostgreSQL use row-locking for UPDATEed rows.
Standard SELECT ignore this lock, but SELECT FOR UPDATE wait until
*other* transaction with UPDATE will commited.
> somewhat like:
> select url,id from mytable for update order by priority,id limit 5;
^^^^^^^^^^^^^^^^^^^
see the SELECT's syntax, ORDER BY must be before FOR UPDATE.
> I want update the id in above return like:
> update mytable set allocatedto='whatever' where id in above return set.
Can't you in UPDATE's WHERE define some 'id' as in above SELECT?
An example (via subselect):
UPDATE mytable SET allocatedto='whatever'
WHERE id IN (
SELECT id FROM mytable ORDER BY priority,id LIMIT 5
);
But it not will too much fast... better is define relevant 'id'
inside UPDATE's WHERE without sub-select, but if you need define it via
ORDER+LIMIT it's impossible.
Karel
^ permalink raw reply [nested|flat] 17+ messages in thread
* SELECT ... FOR UPDATE
@ 2001-03-28 22:53 Marcos Minshew <mkm@outerscape.net>
0 siblings, 1 reply; 17+ messages in thread
From: Marcos Minshew @ 2001-03-28 22:53 UTC (permalink / raw)
To: pgsql-sql
I am interested in using the SELECT ... FOR UPDATE feature but it doesn't
work quite the way I had hoped. If there is a better/different way of doing
this please enlighten me.
If I issue:
BEGIN;
SELECT * FROM atable WHERE atable.key = 10 FOR UPDDATE;
in one session and then issue the same commands from a 2nd session, the 2nd
session simply waits until the 1st session issues COMMIT or ROLLBACK. While
the 2nd session is waiting I am, apparently locked out. What I would like
is for the 2nd session to determine if the lock can be obtained and if not,
offer the user a choice of waiting or escaping from the SELECT. Is this
possible?
Using 7.0.3
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SELECT ... FOR UPDATE
@ 2001-03-29 11:44 Cedar Cox <cedarc@visionforisrael.com>
parent: Marcos Minshew <mkm@outerscape.net>
0 siblings, 0 replies; 17+ messages in thread
From: Cedar Cox @ 2001-03-29 11:44 UTC (permalink / raw)
To: pgsql-sql
On Wed, 28 Mar 2001, Marcos Minshew wrote:
> I am interested in using the SELECT ... FOR UPDATE feature but it doesn't
> work quite the way I had hoped. If there is a better/different way of doing
> this please enlighten me.
>
> If I issue:
>
> BEGIN;
> SELECT * FROM atable WHERE atable.key = 10 FOR UPDDATE;
>
> in one session and then issue the same commands from a 2nd session, the 2nd
> session simply waits until the 1st session issues COMMIT or ROLLBACK. While
> the 2nd session is waiting I am, apparently locked out. What I would like
> is for the 2nd session to determine if the lock can be obtained and if not,
> offer the user a choice of waiting or escaping from the SELECT. Is this
> possible?
>
> Using 7.0.3
(I actually almost wrote an email asking this same question)
Has the documentation been updated recently or something I can find it
elsewhere (ie, not pg manual)? I think (maybe) I am not the only person
confused by documentation for LOCK and MVCC. "lack of detail / examples"
would probably say it best...
Thanks,
-Cedar
^ permalink raw reply [nested|flat] 17+ messages in thread
* SELECT ... FOR UPDATE
@ 2001-04-05 12:29 Loïc Bourgeois <loic.bourgeois@mobileway.com>
0 siblings, 0 replies; 17+ messages in thread
From: Loïc Bourgeois @ 2001-04-05 12:29 UTC (permalink / raw)
To: pgsql-sql
I have to create a program to use the lastest version of PostgreSQL.
Many processes of this program must run in parallele.
In a table I have to select some specific row next insert a new row with
the same search carateristiques
and commit.
But between the select and the insert action none other proccesses
execute the same research parameters (which must return the same lines)
before the commit of the first process. Or the result of the second
select must contain the insert result of the precedent process.
I known I must use SELECT FOR UPDATE and or BEGIN section with lock table.
What is the lock option mode.
Thanks a lot for all the informations.
For resume I would like to do a SELECT ... FOR UPDATE with the option NOWAIT (cf. ORACLE instruction).
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SELECT... FOR UPDATE
@ 2002-02-16 01:49 David Griffiths <dgriffiths@boats.com>
0 siblings, 0 replies; 17+ messages in thread
From: David Griffiths @ 2002-02-16 01:49 UTC (permalink / raw)
To: pgsql-sql
SELECT .... FOR UPDATE;
locks all records returned from the SELECT. Anyone who tries to update any
one of those records will block until you commit, rollback (or the
connection is dropped and a rollback issued).
It will ensure that no-one can modifiy the record(s).
David
"GB Clark" <postgres@vsservices.com> wrote in message
news:20020215155107.1e405665.postgres@vsservices.com...
> Hi,
>
> Can anyone give me a couple of examples or a explanation of select for
update is supposed to work?
>
> I've looked in the manual and the locking stuff is confusing (or it could
just be me not understanding..))
> and neither of my SQL books go into any type of detail on locking.
>
> What I want to do is select a record, do something and then update that
record to reflect the outcome
> of the processing with 0% chance of another process doing the same thing.
Is this possiable?
>
> my idea:
>
> BEGIN
> SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
> -- Do processing here
> UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
> COMMIT
>
> Just looking for more information....
>
> Thanks,
>
> GB
>
> --
> GB Clark II | Roaming FreeBSD Admin
> gclarkii@VSServices.COM | General Geek
> CTHULU for President - Why choose the lesser of two evils?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 17+ messages in thread
* SELECT FOR UPDATE
@ 2002-03-12 01:31 feblec@ig.com.br
0 siblings, 2 replies; 17+ messages in thread
From: feblec@ig.com.br @ 2002-03-12 01:31 UTC (permalink / raw)
To: pgsql-sql
Any example ? Or this is select not implemented ? ( V7.2 )
_________________________________________________________
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://registro.ig.com.br/censo/igmail
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SELECT FOR UPDATE
@ 2002-03-12 09:18 Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
parent: feblec@ig.com.br
1 sibling, 0 replies; 17+ messages in thread
From: Florian Weimer @ 2002-03-12 09:18 UTC (permalink / raw)
To: pgsql-sql
feblec@ig.com.br writes:
> Any example ? Or this is select not implemented ? ( V7.2 )
Last time I tried, it was implemented. It even seems to result in the
proper serialization.
Why do you think it is not implemented?
--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT +49-711-685-5973/fax +49-711-685-5898
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: SELECT FOR UPDATE
@ 2002-03-12 17:46 Andrew G. Hammond <drew@xyzzy.dhs.org>
parent: feblec@ig.com.br
1 sibling, 0 replies; 17+ messages in thread
From: Andrew G. Hammond @ 2002-03-12 17:46 UTC (permalink / raw)
To: feblec@ig.com.br; +Cc: pgsql-sql
On Mon, Mar 11, 2002 at 10:31:32PM -0300, feblec@ig.com.br wrote:
> Any example ? Or this is select not implemented ? ( V7.2 )
It's implemented, and has been for a while. Here's a trivial example.
BEGIN;
SELECT * FROM stuff WHERE stuff_id = '1' FOR UPDATE;
-- row 1 is now locked (exclusive), so we can do what needs doing
UPDATE stuff SET something = 'whatever' WHERE stuff_id = '1';
COMMIT;
This is covered in the manual, see
http://www2.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-select.html
I don't mind answering newbie questions like this in the appropriate
forum (pgsql-novice list for example). In the future please at least
check the manual or give it a try before posting. :)
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
^ permalink raw reply [nested|flat] 17+ messages in thread
* select for update
@ 2002-11-21 05:20 Justin Georgeson <jgeorgeson@lopht.net>
0 siblings, 1 reply; 17+ messages in thread
From: Justin Georgeson @ 2002-11-21 05:20 UTC (permalink / raw)
To: pgsql-sql
I'm pretty new to databases in general, and would like to find a spiffy
way to do something. I want to use two columns from one table to
populate three columns in two other tables. Assuming t1 is the table I
want to take the values from, here is the structure of what I want to
insert into t2 and t3.
t2.id = t1.id
t2.groupname = t1.username
t2.owner = t1.username
t3.id = <next available>
t3.groupid = t1.id
t3.username = t1.username
t3.writeperms = 31
PS - I'm not subscribed to the list, so please CC my email with responses.
--
; Justin Georgeson
; http://www.lopht.net
; mailto:jgeorgeson@lopht.net
; "Free the mallocs, delete the news"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: select for update
@ 2002-11-26 16:00 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Justin Georgeson <jgeorgeson@lopht.net>
0 siblings, 0 replies; 17+ messages in thread
From: Stephan Szabo @ 2002-11-26 16:00 UTC (permalink / raw)
To: Justin Georgeson <jgeorgeson@lopht.net>; +Cc: pgsql-sql
On Wed, 20 Nov 2002, Justin Georgeson wrote:
> I'm pretty new to databases in general, and would like to find a spiffy
> way to do something. I want to use two columns from one table to
> populate three columns in two other tables. Assuming t1 is the table I
> want to take the values from, here is the structure of what I want to
> insert into t2 and t3.
>
> t2.id = t1.id
> t2.groupname = t1.username
> t2.owner = t1.username
>
> t3.id = <next available>
> t3.groupid = t1.id
> t3.username = t1.username
> t3.writeperms = 31
If you're trying to populate the entire table and t3.id is a serial, I
think you could do:
insert into t2 (id, groupname, owner) select id, groupname, username from
t1;
insert into t3 (groupid, username, writeperms) select id, username, 31
from t1;
If you mean that on inserts to t1 you want to make rows in the other
tables then you probably want a simple trigger function like (untested):
create function t1_make_t2_and_t3() returns OPAQUE as '
BEGIN
INSERT INTO t2 (id, groupname, owner) values (NEW.id, NEW.username,
NEW.username);
INSERT INTO t3 (groupid, username, writeperms) values (NEW.id,
NEW.username, 31);
return NEW;
END;' language 'plpgsql';
create trigger t1_make_t2_and_t3_trig after insert on t1 for each row
execute procedure t1_make_t2_and_t3();
^ permalink raw reply [nested|flat] 17+ messages in thread
* Select for update
@ 2021-05-26 09:26 Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Yambu @ 2021-05-26 09:26 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hello
May i know if i run the update below if the row select in SELECT FOR
UPDATE will not be available for selection during update?
UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
status_id=1 LIMIT 1) RETURNING id into v_id;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Select for update
@ 2021-05-26 12:27 David G. Johnston <david.g.johnston@gmail.com>
parent: Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2021-05-26 12:27 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Wednesday, May 26, 2021, Yambu <hyambu@gmail.com> wrote:
> Hello
>
> May i know if i run the update below if the row select in SELECT FOR
> UPDATE will not be available for selection during update?
>
> UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
> status_id=1 LIMIT 1) RETURNING id into v_id;
>
Which update? The way it works is by locking - Ihe row is available but
locked and may require waiting.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Select for update
@ 2021-05-26 15:19 Yambu <hyambu@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Yambu @ 2021-05-26 15:19 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
This update, sorry forgot to include FOR UPDATE part
UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
status_id=1 LIMIT 1 *FOR UPDATE*) RETURNING id into v_id;
On Wed, May 26, 2021 at 2:27 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
>
> On Wednesday, May 26, 2021, Yambu <hyambu@gmail.com> wrote:
>
>> Hello
>>
>> May i know if i run the update below if the row select in SELECT FOR
>> UPDATE will not be available for selection during update?
>>
>> UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
>> status_id=1 LIMIT 1) RETURNING id into v_id;
>>
>
> Which update? The way it works is by locking - Ihe row is available but
> locked and may require waiting.
>
> David J.
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Select for update
@ 2021-05-26 17:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2021-05-26 17:06 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Wed, May 26, 2021, 08:20 Yambu <hyambu@gmail.com> wrote:
> This update, sorry forgot to include FOR UPDATE part
>
> UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
> status_id=1 LIMIT 1 *FOR UPDATE*) RETURNING id into v_id;
>
> On Wed, May 26, 2021 at 2:27 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>>
>> On Wednesday, May 26, 2021, Yambu <hyambu@gmail.com> wrote:
>>
>>> Hello
>>>
>>> May i know if i run the update below if the row select in SELECT FOR
>>> UPDATE will not be available for selection during update?
>>>
>>> UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
>>> status_id=1 LIMIT 1) RETURNING id into v_id;
>>>
>>
>> Which update? The way it works is by locking - Ihe row is available but
>> locked and may require waiting.
>>
>> David J.
>>
>
I don't think there is a point in saying for update when you are executing
an update command.
David J.
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Select for update
@ 2021-05-26 17:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2021-05-26 17:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Yambu <hyambu@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, May 26, 2021, 08:20 Yambu <hyambu@gmail.com> wrote:
>> UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
>> status_id=1 LIMIT 1 *FOR UPDATE*) RETURNING id into v_id;
> I don't think there is a point in saying for update when you are executing
> an update command.
The FOR UPDATE is in a subselect, so what it's doing is locking
rows of table2. That seems fairly sensible if what you want to
do is make sure those rows don't change before the UPDATE
commits.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2021-05-26 17:36 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-15 05:51 select [for update]?? Matthew Hagerty <matthew@venux.net>
2000-12-12 22:03 select ... for update Jie Liang <jliang@ipinc.com>
2000-12-13 08:28 ` Re: select ... for update Karel Zak <zakkr@zf.jcu.cz>
2001-03-28 22:53 SELECT ... FOR UPDATE Marcos Minshew <mkm@outerscape.net>
2001-03-29 11:44 ` Re: SELECT ... FOR UPDATE Cedar Cox <cedarc@visionforisrael.com>
2001-04-05 12:29 SELECT ... FOR UPDATE Loïc Bourgeois <loic.bourgeois@mobileway.com>
2002-02-16 01:49 Re: SELECT... FOR UPDATE David Griffiths <dgriffiths@boats.com>
2002-03-12 01:31 SELECT FOR UPDATE feblec@ig.com.br
2002-03-12 09:18 ` Re: SELECT FOR UPDATE Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
2002-03-12 17:46 ` Re: SELECT FOR UPDATE Andrew G. Hammond <drew@xyzzy.dhs.org>
2002-11-21 05:20 select for update Justin Georgeson <jgeorgeson@lopht.net>
2002-11-26 16:00 ` Re: select for update Stephan Szabo <sszabo@megazone23.bigpanda.com>
2021-05-26 09:26 Select for update Yambu <hyambu@gmail.com>
2021-05-26 12:27 ` Re: Select for update David G. Johnston <david.g.johnston@gmail.com>
2021-05-26 15:19 ` Re: Select for update Yambu <hyambu@gmail.com>
2021-05-26 17:06 ` Re: Select for update David G. Johnston <david.g.johnston@gmail.com>
2021-05-26 17:36 ` Re: Select for update Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox