Subject: 10000 appends (followup)

From: [email-masked] (Steve Davidson)
Date: Wed, 9 Jun 93 14:25:47 -0700

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")

---

From: George Hartzell <[email-masked]>
Date: Wed, 9 Jun 93 18:41:54 -0700

>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                                     [email-masked]
Computer Science, 570 Evans Hall,
University of California, Berkeley, CA 94720