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