Skip site navigation (1) Skip section navigation (2)

Re: background jobs

From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: "Kumar S" <ps_postgres(at)yahoo(dot)com>, "Michael Fuhr" <mike(at)fuhr(dot)org>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: background jobs
Date: 2005-03-06 18:19:51
Message-ID: 002501c52279$16ac6070$1f6df345@WATSON (view raw or flat)
Thread:
Lists: pgsql-novice
Did you want to give us a sample of what you are trying to do?  Example 
table structures, your INSERT statement, and an EXPLAIN on one of them?  If 
you are using selects, it could be as simple as indexing some columns to 
make this faster....

Sean

----- Original Message ----- 
From: "Kumar S" <ps_postgres(at)yahoo(dot)com>
To: "Michael Fuhr" <mike(at)fuhr(dot)org>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Sunday, March 06, 2005 11:05 AM
Subject: Re: [NOVICE] background jobs


> Hi ,
>
> thank you very much for your helpful comments.
>
> I cannot use COPY because I am using a many SELECT
> statements in INSERT statements.
>
> also one more clarification:
>
> is the syntax correct for a transaction:
>
>
> BEGIN TRANSACTION;
> INSERT .........
> INSERT.......
> INSERT.......
> 100 k LINES
>
> in case if want to rollback:
>
> # ROLLBACK;
>
>
> Thank you.
>
>
>
>
>
> --- Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>> On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S
>> wrote:
>>
>> >  I am using a laptop and some of my .sql files
>> with
>> > insert statements (300K insert statements) take
>> very
>> > long time.
>>
>> How long is "very long"?  Have you seen the
>> "Performance Tips" chapter
>> in the documentation, in particular the "Populating
>> a Database" section?
>>
>>
> http://www.postgresql.org/docs/8.0/interactive/populate.html
>>
>> Are you using transactions?  Wrapping a lot of
>> INSERTs in a transaction
>> should be significantly faster than doing each
>> INSERT as its own
>> transaction.
>>
>> Have you considered using COPY instead of INSERT?
>> COPY should be
>> significantly faster than INSERT for bulk loads.
>>
>> > Could any one help to setup a background job where
>> I
>> > can start a job and forget until the next morning
>> to
>> > see that the job is finished.
>> >
>> > Should it be something like the following:
>> > myserver$ psql -d mydb -d node2|\i /insert.sql &>
>>
>> See the psql documentation and your shell's
>> documentation on how to run
>> a background job and redirect its output; you might
>> also need to use
>> "nohup".  For example, the following might work in
>> Bourne-like shells
>> (bash, sh, ksh, etc.):
>>
>> nohup psql -d mydb -f insert.sql > insert.log 2>&1 &
>>
>> -- 
>> Michael Fuhr
>> http://www.fuhr.org/~mfuhr/
>>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
> 



In response to

pgsql-novice by date

Next:From: Wolfgang KellerDate: 2005-03-06 18:56:46
Subject: Python modules for PL/Python?
Previous:From: Kumar SDate: 2005-03-06 16:05:44
Subject: Re: background jobs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group