From pacquet@newsguy.com Tue Aug 13 21:41:35 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2C60047595D for ; Tue, 13 Aug 2002 17:41:35 -0400 (EDT) Received: from newsguy.com (smtp.newsguy.com [216.148.53.69]) by postgresql.org (Postfix) with ESMTP id 7A99947591C for ; Tue, 13 Aug 2002 17:41:34 -0400 (EDT) Received: (from pacquet@localhost) by newsguy.com (8.9.1a/8.9.1) id OAA71833 for pgsql-interfaces@postgresql.org.; Tue, 13 Aug 2002 14:41:36 -0700 (PDT) Date: Tue, 13 Aug 2002 14:41:36 -0700 (PDT) Message-Id: <200208132141.OAA71833@newsguy.com> X-Mailer: Direct Read Email by Newsguy News Service To: pgsql-interfaces@postgresql.org From: pacquet@newsguy.com Subject: PQputline in BINARY mode? X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/36 X-Sequence-Number: 2998 OK, I've tested both a COPY BINARY FROM which was very fast, and now I've also done: PQputline(conn, ) the question is, can I combine both techniques, and use a programmatic interface to copy binary information? We have an data collection application which needs to insert a huge amount of (binary) information very quickly, so I'm interested in squeezing out every last CPU cycle. (any other tips you have will also be appreciated). Thanks From tgl@sss.pgh.pa.us Tue Aug 13 21:54:00 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D386247591C for ; Tue, 13 Aug 2002 17:54:00 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 0E48D4758BB for ; Tue, 13 Aug 2002 17:54:00 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g7DLrjVk001624; Tue, 13 Aug 2002 17:53:45 -0400 (EDT) To: pacquet@newsguy.com Cc: pgsql-interfaces@postgresql.org Subject: Re: PQputline in BINARY mode? In-reply-to: <200208132141.OAA71833@newsguy.com> References: <200208132141.OAA71833@newsguy.com> Comments: In-reply-to pacquet@newsguy.com message dated "Tue, 13 Aug 2002 14:41:36 -0700" Date: Tue, 13 Aug 2002 17:53:44 -0400 Message-ID: <1623.1029275624@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/38 X-Sequence-Number: 3000 pacquet@newsguy.com writes: > the question is, can I combine both techniques, and use a programmatic > interface to copy binary information? Nope. There's no support for binary COPY in the frontend/backend protocol. This is one of the things I'd like to see fixed next time we change the protocol. (That's a sufficiently painful thing that we don't do it often --- the last time was release 6.4 --- but we're considering a protocol rev for the next development cycle, 7.4.) regards, tom lane From pacquet@newsguy.com Tue Aug 13 22:15:20 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E42ED475A69 for ; Tue, 13 Aug 2002 18:15:20 -0400 (EDT) Received: from newsguy.com (smtp.newsguy.com [216.148.53.69]) by postgresql.org (Postfix) with ESMTP id 53A154759F6 for ; Tue, 13 Aug 2002 18:15:20 -0400 (EDT) Received: (from pacquet@localhost) by newsguy.com (8.9.1a/8.9.1) id PAA79633; Tue, 13 Aug 2002 15:15:11 -0700 (PDT) Date: Tue, 13 Aug 2002 15:15:11 -0700 (PDT) Message-Id: <200208132215.PAA79633@newsguy.com> X-Mailer: Direct Read Email by Newsguy News Service To: tgl@sss.pgh.pa.us, pgsql-interfaces@postgresql.org From: pacquet@newsguy.com Subject: Re: PQputline in BINARY mode? X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/39 X-Sequence-Number: 3001 Thanks. Now I realize what should have been obvious: the command "COPY BINARY" is being executed by the backend, while PQputline() is a frontend routine. Duh. However, this is an embedded system, so some modest amount of backend-hacking is quite acceptable if the payoff is large enough. Do you have any pointers on implementing a variant of COPY BINARY <> FROM file that works out of memory instead of a file? At Tue, 13 Aug 2002 17:53:44 -0400, you wrote >pacquet@newsguy.com writes: >> the question is, can I combine both techniques, and use a programmatic >> interface to copy binary information? > >Nope. There's no support for binary COPY in the frontend/backend >protocol. This is one of the things I'd like to see fixed next time >we change the protocol. (That's a sufficiently painful thing that we >don't do it often --- the last time was release 6.4 --- but we're >considering a protocol rev for the next development cycle, 7.4.) > > regards, tom lane > From tgl@sss.pgh.pa.us Tue Aug 13 22:41:01 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 117C047589C for ; Tue, 13 Aug 2002 18:41:01 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 83B944754A0 for ; Tue, 13 Aug 2002 18:41:00 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g7DMf1Vk002009; Tue, 13 Aug 2002 18:41:01 -0400 (EDT) To: pacquet@newsguy.com Cc: pgsql-interfaces@postgresql.org Subject: Re: PQputline in BINARY mode? In-reply-to: <200208132215.PAA79633@newsguy.com> References: <200208132215.PAA79633@newsguy.com> Comments: In-reply-to pacquet@newsguy.com message dated "Tue, 13 Aug 2002 15:15:11 -0700" Date: Tue, 13 Aug 2002 18:41:01 -0400 Message-ID: <2008.1029278461@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/40 X-Sequence-Number: 3002 pacquet@newsguy.com writes: > However, this is an embedded system, so some modest amount of > backend-hacking is quite acceptable if the payoff is large enough. Do > you have any pointers on implementing a variant of COPY BINARY <> FROM > file that works out of memory instead of a file? I think what you want is to hack the fe/be protocol so you can pass binary data in the COPY from stdin/to stdout datastream. The problem is that the message protocol for COPY is ASCII strings (ugh). Change it to be counted strings and away you go. (There are more changes that I want to see in this area than that, but that should get you the functionality you want...) backend/commands/copy.c and PQgetline/PQputline on the frontend side are the things to look at. Also read the SGML docs' chapter about the fe/be protocol. regards, tom lane