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@aklaver.com