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,
Question
Race condition between two data loading scripts
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.