X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A63B89DC84E for ; Wed, 4 Jan 2006 11:40:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40092-02-3 for ; Wed, 4 Jan 2006 11:40:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.204]) by postgresql.org (Postfix) with ESMTP id 148EE9DCA22 for ; Wed, 4 Jan 2006 11:40:25 -0400 (AST) Received: by xproxy.gmail.com with SMTP id s14so1845626wxc for ; Wed, 04 Jan 2006 07:40:31 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references; b=d3F3KdEbP7pCro4pC2ARXOQJh6MtdHQxf2EuzYgBROOYTzGbYXswm+9C5rQrLaQ9dOtvyAHkfDRiUju3/2p8AkIHBQsDhQy3+Abkpq82FmKI1oR5V1VdmXQwqegKqiVAfjJGQ3OTc1nYauKa0THHRRy46bhtbnw9XuZGNbEfZpw= Received: by 10.70.8.4 with SMTP id 4mr12606172wxh; Wed, 04 Jan 2006 07:40:31 -0800 (PST) Received: by 10.70.14.7 with HTTP; Wed, 4 Jan 2006 07:40:31 -0800 (PST) Message-ID: Date: Wed, 4 Jan 2006 09:40:31 -0600 From: Kelly Burkhart To: Steve Eckmann Subject: Re: improving write performance for logging application Cc: pgsql-performance@postgresql.org In-Reply-To: <43BBD6E2.6080300@computer.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2696_747897.1136389231093" References: <43BB0C5C.8020207@computer.org> <20060104000603.GA29469@uio.no> <43BBD6E2.6080300@computer.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/15 X-Sequence-Number: 16493 ------=_Part_2696_747897.1136389231093 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 1/4/06, Steve Eckmann wrote: > > Thanks, Steinar. I don't think we would really run with fsync off, but I > need to document the performance tradeoffs. You're right that my explanat= ion > was confusing; probably because I'm confused about how to use COPY! I cou= ld > batch multiple INSERTS using COPY statements, I just don't see how to do = it > without adding another process to read from STDIN, since the application > that is currently the database client is constructing rows on the fly. I > would need to get those rows into some process's STDIN stream or into a > server-side file before COPY could be used, right? Steve, You can use copy without resorting to another process. See the libpq documentation for 'Functions Associated with the copy Command". We do something like this: char *mbuf; // allocate space and fill mbuf with appropriately formatted data somehow PQexec( conn, "begin" ); PQexec( conn, "copy mytable from stdin" ); PQputCopyData( conn, mbuf, strlen(mbuf) ); PQputCopyEnd( conn, NULL ); PQexec( conn, "commit" ); -K ------=_Part_2696_747897.1136389231093 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 1/4/06, Steve Eckmann <eckmann@computer.org> wrote:
Thanks, Steinar. I don't think we would really run with fsync off, but I need to document the performance tradeoffs. You're right that my explanation was confusing; probably because I'm confused about how to use COPY! I could batch multiple INSERTS using COPY statements, I just don't see how to do it without adding another process to read from STDIN, since the application that is currently the database client is constructing rows on the fly. I would need to get those rows into some process's STDIN stream or into a server-side file before COPY could be used, right?

Steve,

You can use copy without resorting to another process.  See the libpq documentation for 'Functions Associated with the copy Command".  We do something like this:

char *mbuf;

// allocate space and fill mbuf with appropriately formatted data somehow
PQexec( conn, "begin" );
PQexec( conn, "copy mytable from stdin" );
PQputCopyData( conn, mbuf, strlen(mbuf) );
PQputCopyEnd( conn, NULL );
PQexec( conn, "commit" );

-K
------=_Part_2696_747897.1136389231093--