From: Teemu Torma <teemu@torma.org>
To: pgsql-performance@postgresql.org
Subject: Re: How import big amounts of data?
User-Agent: KMail/1.9.1
References: <43B3B0EA.6070406@andromeiberica.com>
	<200512291541.05739.teemu@torma.org>
	<43B40C8F.6010100@andromeiberica.com>
In-Reply-To: <43B40C8F.6010100@andromeiberica.com>
MIME-Version: 1.0
Content-Disposition: inline
Date: Thu, 29 Dec 2005 18:05:45 +0100
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 8bit
Message-Id: <200512291805.45684.teemu@torma.org>

On Thursday 29 December 2005 17:19, Arnau wrote:
> > - Use plpgsql function to do the actual insert (or update/insert if
> > needed).
> >
> > - Inside a transaction, execute SELECT statements with maximum
> > possible number of insert function calls in one go.  This minimizes
> > the number of round trips between the client and the server.
>
> Thanks Teemu! could you paste an example of one of those functions?
> ;-) An example of those SELECTS also would be great, I'm not sure I
> have completly understood what you mean.

An insert function like:

CREATE OR REPLACE FUNCTION
insert_values (the_value1 numeric, the_value2 numeric)
RETURNS void
LANGUAGE plpgsql VOLATILE AS $$
BEGIN
  INSERT INTO values (value1, value2)
    VALUES (the_value1, the_value2);
RETURN;
END;
$$;

Then execute queries like

SELECT insert_values(1,2), insert_values(2,3), insert_values(3,4);

with maximum number of insert_values calls as possible.

I think the transaction (BEGIN/COMMIT) has little time benefit if you 
have at least hundreds of calls in one SELECT.

Teemu