Re: background jobs - Mailing list 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@yahoo.com>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: <pgsql-novice@postgresql.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@fuhr.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
>