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 BBB199DC802
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  4 Jan 2006 20:19:14 -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 29120-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  4 Jan 2006 20:19:16 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mtiwmhc13.worldnet.att.net (mtiwmhc13.worldnet.att.net
	[204.127.131.117])
	by postgresql.org (Postfix) with ESMTP id C563E9DC817
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Jan 2006 20:19:11 -0400 (AST)
Received: from [10.10.10.3]
	(98.denver-06rh15rt.co.dial-access.att.net[12.73.181.98])
	by worldnet.att.net (mtiwmhc13) with ESMTP
	id <20060105001912113005dguce>; Thu, 5 Jan 2006 00:19:13 +0000
Message-ID: <43BC65FC.3000903@computer.org>
Date: Wed, 04 Jan 2006 17:19:08 -0700
From: Steve Eckmann <eckmann@computer.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Kelly Burkhart <kelly@kkcsm.net>
CC: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
References: <43BB0C5C.8020207@computer.org> <20060104000603.GA29469@uio.no>	
	<43BBD6E2.6080300@computer.org>
	<fa1e4ce70601040740t7f20ac13w9fddc80c10921c6@mail.gmail.com>
In-Reply-To: <fa1e4ce70601040740t7f20ac13w9fddc80c10921c6@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------040705060505070209090406"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.499 required=5 tests=[AWL=0.019,
	DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001]
X-Spam-Score: 0.499
X-Spam-Level: 
X-Archive-Number: 200601/19
X-Sequence-Number: 16497

This is a multi-part message in MIME format.
--------------040705060505070209090406
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Kelly Burkhart wrote:

> On 1/4/06, Steve Eckmann <eckmann@computer.org 
> <mailto: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

Thanks for the concrete example, Kelly. I had read the relevant libpq 
doc but didn't put the pieces together.

Regards,  Steve

--------------040705060505070209090406
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Kelly Burkhart wrote:<br>
<blockquote
 cite="midfa1e4ce70601040740t7f20ac13w9fddc80c10921c6@mail.gmail.com"
 type="cite">On 1/4/06, <b class="gmail_sendername">Steve Eckmann</b>
&lt;<a href="mailto:eckmann@computer.org">eckmann@computer.org</a>&gt;
wrote:
  <div><span class="gmail_quote"></span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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?</blockquote>
  <div><br>
Steve,<br>
  <br>
You can use copy without resorting to another process.&nbsp; See the
libpq documentation for 'Functions Associated with the copy
Command".&nbsp; We do something like this:<br>
  <br>
  </div>
  </div>
char *mbuf;<br>
  <br>
// allocate space and fill mbuf with appropriately formatted data
somehow<br>
  <br>
PQexec( conn, "begin" );<br>
PQexec( conn, "copy mytable from stdin" );<br>
PQputCopyData( conn, mbuf, strlen(mbuf) );<br>
PQputCopyEnd( conn, NULL );<br>
PQexec( conn, "commit" );<br>
  <br>
-K<br>
</blockquote>
Thanks for the concrete example, Kelly. I had read the relevant libpq
doc but didn't put the pieces together.<br>
<br>
Regards,&nbsp; Steve<br>
</body>
</html>

--------------040705060505070209090406--