Race condition between two data loading scripts
I have one process that runs every minute and does COPY into a staging table of data from files.
I have a second process that runs once an hour that does the following:
SET SESSION AUTOCOMMIT TO OFF;
LOCK TABLE my_staging_table;
MERGE into full_table USING my_staging_table
I dont have a way to synchronize these 2 processes. I see that some records are being lost. From the timestamps and analysis it looks like the COPY insert some data into the staging table at the same time as the TRUNCATE and the TRUNCATE happens AFTER the COPY.
I tried adding a LOCK TABLE my_staging_table before the COPY but it did not help.
How can I rewrite this so that I dont loose data? Maybe with table renaming?