Return-Path: mao
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA06780; Thu, 10 Jun 93 09:47:19 -0700
Message-Id: <9306101647.AA06780@postgres.Berkeley.EDU>
From: Mike Olson <mao@postgres.Berkeley.EDU>
Subject: Re: 1000 appends (This should not be cut off)
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: ruby!imram@colossus.apple.com's message of 9 Jun 1993 22:41 PDT
Date: Thu, 10 Jun 1993 09:48:41 -0700
From: Mike Olson <mao@postgres.Berkeley.EDU>

ruby!imram@colossus.apple.com (Steve Davidson) writes:

> I attempted to append 10000 records/tuples to a Postgres
> database.  After 2098 appends I get the following error:
>  
> 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") ;

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
					mao@cs.berkeley.edu