Re: drop view and recreate - for sync

Vick Khera wrote:
> On Fri, Oct 23, 2009 at 6:44 PM, Sydney Puente <sydneypuente(at)yahoo(dot)com> wrote:
>> Ah I see what you mean - thanks very much that is v helpful!
>> Yes the copy will be read-only.
>> Will have 3 tables of data, being read (readonly) and in the background
>> Will have 3 shadow tables populated from an unreliable db over an unreliable
>> network.
>> not quite sure how I can "insert all the rows" in sql.
>> have postgres 8.03 BTW.
>>
>
> If your network is unreliable, then perhaps the thing to do is have
> your program first fetch all the new data over that network into tab
> delimited files, one per table. Then to insert all your data, just
> use the "COPY" command in postgres to read it all as one hunk of data.

Doing things this way will also let you avoid having the shadow tables.
You can copy the data to the server as a simple file using an
error-tolerant tool that can resume uploads. Ftp with ssl is one option
- but please don't use plain ol' insecure FTP.

Once the csv file has made it to the Pg server host, by using the 'COPY'
command you can load it into the target tables as part of one
transaction. Your apps will see the update as atomic.

Assuming your data files are comma-separated:

BEGIN;
TRUNCATE TABLE data1, data2, data3;
COPY data1 FROM '/path/to/data1.csv' WITH CSV;
COPY data2 FROM '/path/to/data2.csv' WITH CSV;
COPY data3 FROM '/path/to/data3.csv' WITH CSV;
COMMIT; -- At this moment your other apps suddenly see the changes

--
Craig Ringer