Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1llxSi-0006aI-I2
	for pgsql-sql@arkaria.postgresql.org; Wed, 26 May 2021 17:36:56 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1llxSh-0004vJ-Fp
	for pgsql-sql@arkaria.postgresql.org; Wed, 26 May 2021 17:36:55 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1llxSh-0004uM-8e
	for pgsql-sql@lists.postgresql.org; Wed, 26 May 2021 17:36:55 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1llxSe-0002EZ-Vd
	for pgsql-sql@lists.postgresql.org; Wed, 26 May 2021 17:36:54 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 14QHaoHF1549337;
	Wed, 26 May 2021 13:36:50 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Yambu <hyambu@gmail.com>, pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Select for update
In-reply-to: 
 <CAKFQuwaPC=DJA2LzfyhtMr-VAo55wPrwMBKQONNJ3W7Rws365A@mail.gmail.com>
References: 
 <CALhHtcA1f=mVPZ9adDTcCX2E0Zo57ZfMqs4ej=5G+OQA6N-zTA@mail.gmail.com>
 <CAKFQuwYiJyPu2vezTJHzDNqM0+Er-NMf_doDjhiZmWToVhJttQ@mail.gmail.com>
 <CALhHtcDCyQxmSmGPF5du=Gz4fZU0eq22WbcwjLHp8Z4UjOTT-g@mail.gmail.com>
 <CAKFQuwaPC=DJA2LzfyhtMr-VAo55wPrwMBKQONNJ3W7Rws365A@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 26 May 2021 10:06:49 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1549335.1622050610.1@sss.pgh.pa.us>
Date: Wed, 26 May 2021 13:36:50 -0400
Message-ID: <1549336.1622050610@sss.pgh.pa.us>
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/1549336.1622050610%40sss.pgh.pa.us>
Precedence: bulk

"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