Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a daily routine, I need to drop and do a bulk load of a database.

The problem is that I have a WebApp relying on that database. So I can't drop the database just like this.

What would be a good way to tackle this problem ?

share|improve this question
2  
No offense, but I wouldn't suggest doing a bulk load of the db every day. Find a way to merge the changes from your source data, and then, voila, no webapp downtime. –  Kerri Shotts Jul 7 '11 at 2:51
add comment

6 Answers

up vote 4 down vote accepted

Well, this is straightforward in most databases. While your app is running, from your loading job:

  1. Start a transaction (this may be implicit or may require a command, you don't specify which DB you are using)
  2. DELETE all the data from the tables (not drop or truncate)
  3. INSERT the new data
  4. COMMIT the transaction

While steps 1-3 are running, the app will see the "old" version of the data and can continue as normal. The instant step 4 completes, the app will see the new data as soon as it runs the next query.

share|improve this answer
    
the app will see the new data as soon as it runs the next query => when it starts the next transaction. –  ik_zelf Jul 8 '11 at 12:01
add comment

Do you need to drop and reload the database? In a case like yours, I would look at a way to synchronize the database with the reference set (the data you are loading).

An alternative approach would be to have two databases. Load a different one each day. Configure the webapp so that you can swap databases on the fly.

share|improve this answer
add comment

Truncate the tables, then bulk load the data. If needed lock the web app from the table before truncating the table.

share|improve this answer
add comment

this depends on how the dependency trees are. If you have lots of dependend objects on the table[s] where you load the data in, best is not to change the object, the table because lots of invalidations will take place.

truncate table gets rid of the data very fast but won't work when referential constraints are in place. It also won't work when the app is running because of locking issues.

Best for the application will be a normal delete/insert sequence. Not the fastest option but it is best for availabillity and has the least problems with locking.

Other options can be partition exchange loading. Constraints can be problematic and take a lot of time to validate. Never ever drop a table just to get rid of the data. A table is to be considered as application infrastructure and should allways be available, because the application will throw lots of errors to your users.

share|improve this answer
add comment
  1. Stop the webapp.
  2. Drop/restore the DB.
  3. Start the webapp again.
share|improve this answer
1  
My intention is to find a way to never stop the WebApp :) –  Spredzy Jul 5 '11 at 12:40
    
You might not be able to drop the DB unless you stop the WebApp. And what will your users see? Drop/restore is not atomic operation. –  alex Jul 5 '11 at 14:04
1  
What bothering me is that if I stop the WebApp it won't be accesible, somthing that I'd like to avoid. –  Spredzy Jul 5 '11 at 14:14
    
Well if you could put the app in read-only mode and point it to a clone of your DB... –  alex Jul 5 '11 at 17:07
add comment

i load the new data in a temporary table. this way, i have to lock only the rows, that i need to update. Afterwards i delete missing rows and insert new rows.

create table permanent ( key int primary key, field1 int, field2 text );
create temporary table temporary as select * from permanent where false;
\copy temporary from 'actual_data.dump'
alter table temporary add constraint temporary_pkey primary key ( key );

update permanent                              
   set field1 = temporary.field1,
       field2 = temporary.field2
  from temporary
 where permanent.key = temporary.key
   and (     permanent.field1 is distinct from temporary.field1
         or  permanent.field2 is distinct from temporary.field2
       );

delete from permanent where not exists ( select 1
                                           from temporary
                                          where key = permanent.key );
insert into permanent ( select *
                          from temporary
                         where not exists ( select 1
                                              from permanent
                                             where key = temporary.key ) );

this works at least for postgresql.

for oracle i use the following:

update ( select p.field1 p_field1,
                p.field2 p_field2,
                t.field1 t_field1,
                t.field2 t_field2
           from permanent p, temporary t
          where p.key = t.key )
   set p_field1 = t_field1,
       p_field2 = t_field2
 where p_field1 <> t_field1
    or p_field1 is null and t_field1 is not null
    or p_field1 is not null and t_field1 is null
    or p_field2 <> t_field2
    or p_field2 is null and t_field2 is not null
    or p_field2 is not null and t_field2 is null
when i have impossible values, i may use NVL instead:

 where nvl(p_field1, -1) <> nvl(t_field1, -1)
    or nvl(p_field2, '{null}') <> nvl(t_field2, '{null}')
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.