Race condition between two data loading scripts

brocha

Hello
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
TRUNCATE 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?
Thanks,

mosheg

Hi Brocha,
As per our talk try to RENAME the table in the second process that runs once an hour.

Arumugaguru_M

Can you try running the second process statement with FOR UPDATE, something like this "MERGE into full_table USING my_staging_table FOR UPDATE" and see if that helps.