Re: Long running INSERT+SELECT query

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(dot)klaver(at)aklaver(dot)com