Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select for update

Select for update

From
Yambu
Date:
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;

Re: Select for update

From
"David G. Johnston"
Date:

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.

Re: Select for update

From
Yambu
Date:
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.

Re: Select for update

From
"David G. Johnston"
Date:


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.

Re: Select for update

From
Tom Lane
Date:
"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