Re: Long running INSERT+SELECT query

On 04/26/2018 01:32 PM, Vitaliy Garnashevich wrote:
>
>> 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?

The procedure seems to be fighting itself. There is an inherent conflict
between trying to keep up with data changes and presenting a consistent
result. Keeping up means constantly updating the aggregation
calculations which in turn means the result will continually changing.
As David and Alvaro have also suggested your best bet is to pick a point
in time and work off that.

>
> Regards,
> Vitaliy
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com