Обсуждение: drop view and recreate - for sync
Hello,
I have a requirement to pull 2 tables of data from an unreliable db and stash the data in postgres on redhat.
Dont imagine the initial load will be be too difficult, gonna use perl DBD to get the data. < 100 000 records in total , 10 -15 fields.
needs sync-ing regularly - every 12 hours maybe. Interesting feature of the data is that there is no primary key.
This data will be accessed a couple of times a second, and I have a cunning plan to have a view that points to the initial dataload, and then load up the new data into a shadow table, drop the view and then recreate it pointing to the shadow table ( which will then no longer be the shadow).
How does that sound? Good?
Syd
I have a requirement to pull 2 tables of data from an unreliable db and stash the data in postgres on redhat.
Dont imagine the initial load will be be too difficult, gonna use perl DBD to get the data. < 100 000 records in total , 10 -15 fields.
needs sync-ing regularly - every 12 hours maybe. Interesting feature of the data is that there is no primary key.
This data will be accessed a couple of times a second, and I have a cunning plan to have a view that points to the initial dataload, and then load up the new data into a shadow table, drop the view and then recreate it pointing to the shadow table ( which will then no longer be the shadow).
How does that sound? Good?
Syd
Send instant messages to your online friends http://uk.messenger.yahoo.com
On Fri, Oct 23, 2009 at 2:45 AM, Sydney Puente <sydneypuente@yahoo.com> wrote: > This data will be accessed a couple of times a second, and I have a cunning > plan to have a view that points to the initial dataload, and then load up > the new data into a shadow table, drop the view and then recreate it > pointing to the shadow table ( which will then no longer be the shadow). If it is only 100k rows, then within a transaction: 1) delete all rows, 2) insert all new rows, 3) commit, 4) vacuum. don't try to compact the table with cluster or vacuum full since you'll just re-expand it on the next synchronization. There should be no blocking of your read access. This assumes your copy is read-only, which you imply.