This isn't Halloween protection, it's normal UPDATE semantics.
Check out this warning from the docs on UPDATE:
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not ...
Here is an example of using OUTPUT...INTO
drop table if exists InsertTest;
Create Table InsertTest (id int);
drop table if exists UpdateTest;
Create Table UpdateTest (id int);
insert into UpdateTest(id) values(1),(2);
SET id = id + 1
--verify results of insert
select * from ...
Will it always update all rows for the ID to DM = 1 or sometime just
If you don't have NULL values in the ID column then either all DM's for one type of ID != 1 and as a result all ID's that have the same value will be updated or none.
But as said previously, depending on nullability of the ID column you can have suprising results and performance ...
An index only scan can only be used if all columns that the index scan returns are stored in the index.
Yours index lookup_ix contains only the columns natural_key and surrogate_key, but from the execution plan you can see:
-> Index Scan using lookup_ix on public.lookup
Output: lookup.surrogate_key, lookup.ctid, lookup.natural_key
So the index ...
Josh's answer (thanks Josh!) got me thinking about determinism.
I can see for the fundamental usage update table set column = value, when there are duplicate source rows, the question must be answered which matching value from the source will be written to the destination. The simplest implementation would be to let the iterators run to completion. ...
Adding INTO solved part of the problem.
The trick is to move the INSERT operation "into" the OUTPUT INTO clause. The INSERT is "implied", I think because OUTPUT INTO does an INSERT. This only works because I want to do an INSERT with the new ID -- if I had wanted to do an UPDATE this would not work.