Re: BUG #16462: Update Statement destructive behaviour with joins

On Wed, 27 May 2020 at 00:15, PG Bug reporting form
<noreply(at)postgresql(dot)org> 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

David