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 ) id 1jgB55-0002yV-B6 for pgsql-bugs@arkaria.postgresql.org; Tue, 02 Jun 2020 17:52:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jgB53-000114-EZ for pgsql-bugs@arkaria.postgresql.org; Tue, 02 Jun 2020 17:52:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgB53-00010v-84 for pgsql-bugs@lists.postgresql.org; Tue, 02 Jun 2020 17:52:05 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgB51-0002FG-0f for pgsql-bugs@lists.postgresql.org; Tue, 02 Jun 2020 17:52:04 +0000 Received: from bruce by momjian.us with local (Exim 4.92) (envelope-from ) id 1jgB50-00017G-76; Tue, 02 Jun 2020 13:52:02 -0400 Date: Tue, 2 Jun 2020 13:52:02 -0400 From: Bruce Momjian To: David Rowley Cc: srivastava.adi24@gmail.com, PostgreSQL mailing lists Subject: Re: BUG #16462: Update Statement destructive behaviour with joins Message-ID: <20200602175202.GA25612@momjian.us> References: <16462-7160938c9c2989d9@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Wed, May 27, 2020 at 12:48:19AM +1200, David Rowley wrote: > On Wed, 27 May 2020 at 00:15, PG Bug reporting form > wrote: > > Let's say I have a table orange and a temp table temp, and i want to update > > the records after joining the columns in temp table. I used the following > > syntax to update the records which ended up updating the entire table > > "orange". > > > > UPDATE orange > > SET fruit_flag = 'okay' > > FROM temp as t > > INNER JOIN portal_users p on t.fruit_id = p.fruit_id > > WHERE p.id = '123'; > > > > I know that the correct syntax should be the following but judging from the > > destructive nature of this query i honestly feel we should throw validation > > error if the above syntax is not correct. > > That's an unfortunate mistake. > > Unfortunately, SQL is full of these trip hazards. The join syntax was > once revised to try to reduce the pain of accidental cartesian joins > by missed join clauses in the WHERE clause. The JOIN ON syntax was > born because of that. Maybe we didn't get the UPDATE FROM syntax > perfect, as it does still allow users to easily miss the join clause, > but I'm not all that sure what we can realistically do about that, It > does not seem like a good thing to go raising an error as it might > block some genuine use case. > > Thinking back, there was some discussion around looking for ways to > block such mistakes in [1]. As I recall it was going to be an > extension that created triggers to block mistakes like this. However, > that thread has not moved in over 3 years. > > [1] https://www.postgresql.org/message-id/flat/20170202175023.GA30233%40localhost#95ca7fad07b30fd0e2205075f3fc04c5 I have alawys wanted a 'novice' mode which warned/errored on such things. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee