Re: Long running INSERT+SELECT query

On Thu, Apr 26, 2018 at 1:32 PM, Vitaliy Garnashevich <
vgarnashevich(at)gmail(dot)com> wrote:

> 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)
>
​[...]​

> 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.
>

Me, I'd try very hard to design things so the final calculation goes into
an actual results table that omits FK constraints​ and wouldn't try to
"skip if necessary". You are already running an hours-long query - the
users of said information needs to understand that what they are seeing
does not reflect changes in the subsequent hour(s) since it started and
that certain related records being no longer present doesn't detract from
the fact that they were present "back then" and thus represent valid data
at that point in time.

David J.