Delivery-Date: Tue, 12 Apr 94 15:05:47 -0700
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199404121756.KAA08346@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199404121756.KAA16577@faerie.CS.Berkeley.EDU>
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: Mark Victor Priester <priester@student.msu.edu>
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: Another import question 
Reply-To: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
In-reply-to: Your message of Tue, 12 Apr 1994 10:12:36 -0400 (EDT) 
	     <9404121412.AA78322@student4.cl.msu.edu> 
Date: Tue, 12 Apr 94 10:56:14 -0700
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Tue, 12 Apr 94 10:56:17 -0700
Resent-XMts: smtp

Mark Victor Priester <priester@student.msu.edu> writes:
> 1. This works fine for 10 instances/lines even for 100 instances,
>    but when I set it to work on the whole file (~1.5M instances)
>    it chokes early on in the game.

postgres "keeps" too much stuff lying around from command to
command during a single transaction.  you can think of this as 
a memory leak, but it does get cleaned up at end-of-transaction,
so memory leak isn't quite the right way to describe it.  so
running large numbers of commands in a single transaction
(on the order of 10000, depending on how much swap you have) 
causes you to run out of memory.

so: you can load in smaller wads.

since you are turning the values into ascii anyway (so that
you can use "append") it seems like you could just create a 
file in the "copy" (tab-delimited) format and use "copy from".
this is far more efficient than using append commands (no
query parsing/optimization overhead, runs as a single xact).
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki