Re: Long running INSERT+SELECT query

> 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