On further investigation I believe the update itself might be causing the locking. It is on a large table 500+ million rows. As I understand it the table will become locked as soon as SQL starts to find the join. Perhaps the way activity monitor displays the locking is a little misleading though
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. ...
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 ...
It shows it waits on IO, on DataFileRead.
Practically all waits in kernel in io write operations.
These seem contradictory. I assume the first conclusion is coming from pg_stat_activity. Where is the second one coming from?
I quite not understand, why PostgreSQL write something to disk if transaction is not committed yet?
There is only so much ...
Postgres will write data to write ahead log, and to data files during dml operations. There is nothing that works like UNDO tablespace in Oracle.
Taking into account there is no in-place updates in Postgres , updates are just series of inserts and deletes. If fillfactor for the table is set keeping in mind possible updates (not by default, default is 100 ...
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 ...
Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE.
Every single column must be defined for ON DUPLICATE KEY UPDATE to work!
I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement).
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 ...
I finally figured it out. Trick was to check if the users_roles uid column is null. UPDATE users u LEFT JOIN users_roles ur ON u.uid = ur.uid SET status = 0 WHERE u.uid > 1 AND u.status <> 0 AND ur.uid is null AND u.uid <> 99; – Brian N
UPDATE users u
SET u.status = 0
WHERE u.uid > 1
AND u.uid <> 99