Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CABBF476895 for ; Sat, 30 Nov 2002 12:53:41 -0500 (EST) Received: from mpls-qmqp-02.inet.qwest.net (mpls-qmqp-02.inet.qwest.net [63.231.195.113]) by postgresql.org (Postfix) with SMTP id AD4574768A2 for ; Sat, 30 Nov 2002 12:53:02 -0500 (EST) Received: (qmail 92315 invoked by uid 0); 30 Nov 2002 17:42:54 -0000 Received: from unknown (63.231.195.14) by mpls-qmqp-02.inet.qwest.net with QMQP; 30 Nov 2002 17:42:54 -0000 Received: from albqdslgw1poolb164.albq.uswest.net (HELO amadeus) (63.227.105.164) by mpls-pop-14.inet.qwest.net with SMTP; 30 Nov 2002 17:53:08 -0000 Received: from jasons by amadeus with local (Exim 3.35 #1 (Debian)) id 18IBna-0000ka-00 for ; Sat, 30 Nov 2002 10:53:02 -0700 Date: 30 Nov 2002 10:53:02 -0700 Message-ID: <87fztjt05d.fsf@openinformatics.com> From: "Jason E. Stewart" To: pgsql-interfaces@postgresql.org Subject: ["Dean Arnold" ] Re: Array Binding Status? Lines: 97 User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Honest Recruiter) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/2 X-Sequence-Number: 3415 --=-=-= Hi all, I'm included a piece of a discussion that was had on the Perl DBI mailing list a while back. A new feature was added that enable applications to pass off arrays of placeholder values for each placeholder in a prepared SQL statement instead of only a single value for each placeholder. This meant a significant speed-up for things like large INSERT statements. My question is does Postgres have the necessary abilities to handle this feature? Thanks ahead of time, jas. --=-=-= Content-Type: message/rfc822 Content-Disposition: inline X-From-Line: darnold@earthlink.net Fri Mar 08 08:21:24 2002 Return-path: Envelope-to: jasons@localhost Received: from localhost ([127.0.0.1] ident=jasons) by amadeus with esmtp (Exim 3.32 #1 (Debian)) id 16jMBL-0000QF-00 for ; Fri, 08 Mar 2002 08:21:19 -0700 Delivered-To: sjann-charlotte@mail-albq.uswest.net Received: from pop.albq.uswest.net [207.108.240.1] by localhost with POP3 (fetchmail-5.9.5) for jasons@localhost (single-drop); Fri, 08 Mar 2002 08:21:19 -0700 (MST) Received: (qmail 4022 invoked by uid 0); 8 Mar 2002 15:18:45 -0000 Received: from unknown (HELO mail8.uswest.net) (63.226.138.8) by mpls-mailin-12.inet.qwest.net with SMTP; 8 Mar 2002 15:18:45 -0000 Received: (qmail 91128 invoked by uid 0); 8 Mar 2002 15:18:44 -0000 Received: from unknown (HELO albqpop1.albq.uswest.net) (207.108.240.1) by mail8.uswest.net with SMTP; 8 Mar 2002 15:18:44 -0000 Received: (qmail 57193 invoked by alias); 8 Mar 2002 15:18:44 -0000 Delivered-To: alias-openinformatics.com-jason@openinformatics.com Received: (qmail 57190 invoked by uid 0); 8 Mar 2002 15:18:44 -0000 Received: from gull.mail.pas.earthlink.net (HELO gull.prod.itd.earthlink.net) (207.217.120.84) by albqpop1.albq.uswest.net with SMTP; 8 Mar 2002 15:18:44 -0000 Received: from evrtwa1-ar14-4-61-176-036.elnk.dsl.gtei.net ([4.61.176.36] helo=darnold) by gull.prod.itd.earthlink.net with smtp (Exim 3.33 #1) id 16jM8h-00010J-00; Fri, 08 Mar 2002 07:18:36 -0800 Date: Fri, 8 Mar 2002 07:08:35 -0800 Message-ID: <000c01c1c6b3$26d7b140$0b00a8c0@earthlink.net> From: "Dean Arnold" To: "DBI-dev" , Andrew_Brown@adc.com, "Jason E. Stewart" , "Jeff Urlwin" Reply-To: "Dean Arnold" References: Subject: Re: Array Binding Status? Status: U X-UIDL: 1015600725.4038.2780.mpls-mailin-12.inet.qwest.net X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Lines: 36 Xref: amadeus DBI:536 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii > > Thanks Andrew, > > > > The question isn't whether the current DBD::Pg supports the behavior, > > it doesn't, the issue is whether the Postgres C API has an ability to > > do these array-based inserts. > > > > I would definately have to modify DBD::Pg's C code to support this. > > > > I was just wondering from Dean and Jeff what kind of C API methods did > > they need to use to get it working for ODBC. > > It's all part of the ODBC spec/API. > > Jeff > Or check the Oracle OCI array binding (the function names escape me at the moment). The deeper issue is whether the DBMS supports a bulk operation, esp in a transactional context. E.g., Teradata has bulk operation utilities ala SQLLoader, but currently doesn't support bulk operations in a regular SQL connection. While adding an iterative query execution into the C API code will help some, the big boosts occur when the DBMS can gobble up a stream of 1,000+ placeholder tuples at once (and esp. for INSERT, dump 'em directly to disk), as e.g. Oracle and DB2 do. Dean --=-=-=--