Re: Long running INSERT+SELECT query

On Thu, 26 Apr 2018 19:13:17 +0300
Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com> wrote:

> We're trying to populate a table with aggregated data from other
> tables. For that we're running a huge INSERT+SELECT query which joins
> several tables, aggregates values, and then inserts the results into
> another table. The problem we're facing is that while the query is
> running , some records in tables referenced by the results table may
> be deleted, which causes the following error:
>
> ERROR:  insert or update on table "..." violates foreign key
> constraint "..."
> DETAIL:  Key (...)=(...) is not present in table "...".
>
> Who do we make sure that such aggregating query would not fail?

Create a temporary table with a useful subset of the data.

You can select the mininimum number of columns joined and release
the locks. This can also help large queries by giving you a stable
snapshot of the data for repeated queries.

I usually find that pre-joining the tables is easier because
temp tables have restrictions on re-use w/in the query, and also
usually don't have indexes to speed up the joins.

If you are going to run this, say, daily it's easy enough to create
a view and just "create temporary table foo as select * from bar"
for some collection of views and go from there. This makes it easier
to tune the queries on the back end without having to hack the front
end code.

--
Steven Lembark 1505 National Ave
Workhorse Computing Rockford, IL 61103
lembark(at)wrkhors(dot)com +1 888 359 3508