Date: Fri, 21 May 2004 09:38:17 -0500
Subject: Re: ECPG multiple INSERTS or SELECTS in one command?
From: <wespvp@syntegra.com>
To: Michael Meskes <meskes@postgresql.org>
Cc: <pgsql-interfaces@postgresql.org>
Message-Id: <BCD37E89.D866%wespvp@syntegra.com>
In-Reply-To: <20040521135618.GD14285@1>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

>> A (different) Pro*C program I recently ported from Oracle to PostgreSQL
>> showed this difference.  In Pro*C you can load an array with rows to insert,
>> then issue a single INSERT request passing it the array.  I believe the same
>> thing applies to FETCH.  As far as I can tell, in PostgreSQL ecpg (or other)
>> you have to execute one request per record.  The program SEGV'd all over the
>> place when I tried to use the Pro*C array code.  I had to do a major rework
>> of the code to remove the array logic.
> 
> That is for insert right?

Yes, correct.

> Batching inserts is tricky as there is no way
> for ecpg to see if your array of 10 ints shall be inserted as 10 ints or
> as one array.

Perhaps there is some way a flag could be passed as part of the SQL
statement to ecpg to differentiate this?  I suspect (but have no easy way of
proving) the lack of this feature may be why there is a 2:1 difference
between loading the same table with Oracle Pro*C and PostgreSQL (Oracle is
twice as fast).  With Oracle, I populate an array and do a batch load.  With
PostgreSQL, I have to load a row at a time.

> With fetch on the other hand it should work the same as with Pro*C.
> There is no problem to fetch into an array.

Someone on the Interfaces list sent me some sample code.  I had made the
rash assumption that since I had to convert all the Pro*C array (INSERT)
code to non-array, that array usage wasn't supported at all by ecpg.

Wes