Subject: 1000 appends (This should not be cut off)

From: [email-masked] (Steve Davidson)
Date: Wed, 9 Jun 93 22:41:49 -0700

The other meesages may have been cut off by mailers because
I put dots '.' in column 1.  This should have been okay but
several people complained.  Here's the bug report in full
glory.

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")
  .
  .
  .
append CLIENT (id_client=2096,name="dcp",city="XX")
append CLIENT (id_client=2097,name="dcq",city="XX")
append CLIENT (id_client=2098,name="dcr",city="XX")
 
Error: No response from the backend, exiting...

 
The code to do the appends was a tight loop in perl:

    $name = 'aaa' ;
    &PQexec ("begin");
    for ($i = 1; $i <= 10000; $i++) {
        $query = " append CLIENT (id_client=$i,name=\"$name\",city=\"XX\") " ;
        &PQexec($query) ;
        $name++ ;
    }
    &PQexec ("end") ;
 
Why did the backend die?
When I do a query-all on the data I get an infinite loop; no output.
Does anybody know why this is happening?
 
New info: this loop always dies at just over 2000 tuples;
maybe always at exactly 2098.  Notice that this is within
a begin/end transaction and there may be a maximum.  Anyway
I append 2000 records at a time now and it works.

[email-masked]

---

From: Mike Olson <[email-masked]>
Date: Thu, 10 Jun 1993 09:48:41 -0700

[email-masked] (Steve Davidson) writes:

>         $query = " append CLIENT (id_client=$i,name=\"$name\",city=\"XX\") " 
  +;
>     &PQexec ("end") ;

okay, you're running all the appends inside a single transaction.  the
transaction id data structure claims to provide sixteen bits for this,
so you ought to be able to have 65535 commands inside a single xact.

most likely, what's happening is that you're exhausting available memory;
we don't release memory consumed by input tuples until the end of the
transaction.  you should probably break the loop up to do 1K appends at
a time, or so.

as other people have noted, using the copy in command would be much more
efficient than coding this loop yourself.

					mike olson
					project sequoia 2000
					uc berkeley
					[email-masked]