Return-Path: hartzell
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA03380; Wed, 9 Jun 93 18:41:54 -0700
Date: Wed, 9 Jun 93 18:41:54 -0700
Message-Id: <9306100141.AA03380@postgres.Berkeley.EDU>
From: George Hartzell <hartzell@postgres.berkeley.edu>
Subject: Re: 10000 appends (followup)
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
References: <9306092125.AA00812@postgres.Berkeley.EDU>
Reply-To: hartzell@cs.berkeley.edu (George Hartzell)

Steve Davidson writes:
   >I attempted to append 10000 records/tuples to a Postgres
   >database.  After 2098 appends I get the following error:
   > 
   >append CLIENT (id_client=1,name="aaa",city="XX")

It looks like your posting cut off your error message...

While experimenting with stuffing a copy of Genbank into Postgres I
tried to do >> 1000 appends in one swell foop, which caused the back
end to bail out and dump core.  I think that there must be some limit
to the number of appends [commands] that one can do inside a
transaction, and we are both overdoing it.  My solution was to use the
copy interface.  Since I was working in pgperl it was relatively
simple to just change how I was reformating the data before I handed
it to the backend.  I did find that I had to be careful about getting
the format _just right_, since copyin isn't very forgiving.  Newlines
in the data, and backslashes as the last character of a value (which
escapes the column delimiting tab, so you come up an attribute short)
were only some of the fun things to deal with.

Try making each append a separate xact.  If that fixes it, then you
can play with finding some number of appends that you can safely batch
into a single xact to try to get some of your performance back.  Or
you could go to the copy interface, since it's supposed to be faster
for bulk loads anyway.

g.

George Hartzell                                     hartzell@cs.berkeley.edu
Computer Science, 570 Evans Hall,
University of California, Berkeley, CA 94720