Have not worked through all of the above, but a first draft suggestion:
Move the SELECT minus the aggregation functions into a sub-query that
uses FOR UPDATE. Then do the aggregation on the results of the sub-query.
The aggregated table has hundreds of millions of rows, and the query
runs for many hours (which is one of the reasons why it's better not to
fail). I really doubt that row level locking would work. That would be a
lot of RAM just to hold all the locks.
On the other hand, I don't see something like FOR KEY SHARE kind of
locks at table level, so that the query would try not to block most of
other existing activity (e.g. SELECTs, UPDATEs).
Maybe this could be solved by calculating results into a temporary
table, which would not check foreign key constraints, and then copy the
data into the actual results table, while checking each row for FK
consistency and skipping if necessary. But then I don't think it would
be possible for my transaction to see row deletions which other
transactions have done, and to check row existence (the transaction is
there, because the whole thing is implemented as a DO statement with
some local variables).
Thoughts?
Regards,
Vitaliy
