On 04/26/2018 09:13 AM, Vitaliy Garnashevich wrote:
Hi,
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 "...".
SELECT .. FOR UPDATE?:
https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FOR-UPDATE-SHARE
https://www.postgresql.org/docs/10/static/explicit-locking.html#LOCKING-ROWS
Who do we make sure that such aggregating query would not fail?
Regards,
Vitaliy
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
