On Thu, 26 Apr 2018 19:13:17 +0300 Vitaliy Garnashevich <vgarnashevich@xxxxxxxxx> 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@xxxxxxxxxxx +1 888 359 3508
