Postgres Pro
Downloads
Home   >   mailing lists

Thread: drop view and recreate - for sync

drop view and recreate - for sync

From
Sydney Puente
Date:
On Fri, Oct 23, 2009 at 2:45 AM, Sydney Puente <sydneypue...@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 readaccess.  This assumes your
copy is read-only, which you
 imply.
++++++++++++
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.

Syd


Re: drop view and recreate - for sync

From
Vick Khera
Date:
On Fri, Oct 23, 2009 at 6:44 PM, Sydney Puente <sydneypuente@yahoo.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.
 This will be your fastest, most reliable way of loading the data in
minimal time.  Your only other option is to issue 100,000 "INSERT"
statements, which will take much longer.

Re: drop view and recreate - for sync

From
Craig Ringer
Date:
Vick Khera wrote:
> On Fri, Oct 23, 2009 at 6:44 PM, Sydney Puente <sydneypuente@yahoo.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