Subject: Another import question

From: Mark Victor Priester <[email-masked]>
Date: Tue, 12 Apr 1994 10:12:36 -0400 (EDT)

Sorry to beat up on an already battered and bruised topic, but ...

I have a large datafile that I wish to massage into a Postgres database.
I guess the only difference from other posts is that it's not ASCII ;)
I have written a chunk of code that does the following

PQsetdb("blah");
PQexec("begin");

1. read current line of datafile

2. build string of form "append CASE (name = "string, ...)"
   The object CASE already defined in "blah" from monitor.

3. send string to backend with PQexec(string from #2)

PQexec("end");

A couple of comments in no particular order:
(actually they're in numerical order I guess ;) )

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.  Is there something I need to do
   to keep the backend in sync with my process?

2. I have no particular sentimental attachment to this particular approach.
   Any suggestions are welcome.

3. I am running this on SunOS 4.1.1

4. I will be doing this (hopefully) several times in the future and would
   like comments/thoughts on the fastest way to import from a file.

Thanks for you time,

Mark

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Mark Priester               _/  Phone: 517.694.4789                    _/
_/ Barros Research Institute  _/     Fax: 517.694.4788                    _/
_/ 2430 College Road         _/ Internet: [email-masked]        _/
_/ Holt, MI 48842           _/            [email-masked]  _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

---

From: [email-masked] (Paul M. Aoki)
Date: Tue, 12 Apr 94 10:56:14 -0700

Mark Victor Priester <[email-masked]> writes:
>    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  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki