Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 654A6B5DCC0 for ; Tue, 16 Aug 2011 15:40:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 51309-06-3 for ; Tue, 16 Aug 2011 18:40:23 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm15.bullet.mail.bf1.yahoo.com (nm15.bullet.mail.bf1.yahoo.com [98.139.212.174]) by mail.postgresql.org (Postfix) with SMTP id 349EFB5DCCB for ; Tue, 16 Aug 2011 15:40:18 -0300 (ADT) Received: from [98.139.214.32] by nm15.bullet.mail.bf1.yahoo.com with NNFMP; 16 Aug 2011 18:40:18 -0000 Received: from [98.139.212.212] by tm15.bullet.mail.bf1.yahoo.com with NNFMP; 16 Aug 2011 18:40:18 -0000 Received: from [127.0.0.1] by omp1021.mail.bf1.yahoo.com with NNFMP; 16 Aug 2011 18:40:18 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 470040.45585.bm@omp1021.mail.bf1.yahoo.com Received: (qmail 35942 invoked by uid 60001); 16 Aug 2011 18:40:18 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1313520018; bh=dKGhQqu1G1p9AI1bHcwy3Y8qh1cHbTJmwQ2rIvVLOJc=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=hN01Jz/tlS1fcfr1YpP1BWSXD6kz83vdZyjv8cGMrvKlg89k3Qc5rAz/mBhpNSVcLq6hGDslEUgjLQ4FTzBY6LYdX8iZO3iGAflQK+Z995EB4QAa7nbZh57OS8mkRUHsV3Hc2iPwV5tmcqmjekuKXfQiseQoXB/P7LJf64SxoTE= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=ZtGx0dwm4NLDJc+1EnTWcSBIBzSiqbCopzmhaandL5m2D7szixtdNraq/YF+jbgWCMVN+GkLFAwZHjvC+jJzOH56XmEjWSVQQZt8vvHZqIwPTICxenObxvSQhwOr8sj1p8Vu5VSHZQLWB2+XeCnQEO0K/z5u/NvJfDW6k3ZLuZo=; X-YMail-OSG: WMVaUEsVM1lfsYSi9tP2kB9CkgumrTUD4PVYs6t5TjjUWTa O4Q7Ej01vzey6A0bNZIPsLiwoIOjk9Ul0Uh9sPlYA7YR7SknCKn4oauKrJuX .0X5LtdxETDc_YHNJjlsT6ABdYwdFxUT6txoh9f03.ub_AvFDqT7ae.6vUu0 AlNnvsn6GmyRi0OFiNTNT3eKbZ69D.eUFRCOeKbXvAlkMiOwjMu6Eu9_rz1W lyY9daO1MhOMn_fB3T78x9ZU..E.evRoH_6LF8IGqw0LD_OsXnm2aCl_wYk. uY4x3Ztdoirv7PA4aoReULz.Gw6JVCxVcztM6oiRbeNaPbJi8FwC9JnVPLBF kIei2iJjfhkRBGFdEb0ncKsRpvxMuZGeLtU.62dBBUAFJOPig0xuFqrIXmAc taGBzCGo0S.pzvsFIYIX5qvIpGODIH2MFzgDQdPXFzFXAebFuiXJLBB0qAzY pdh.Au83nWapCfEL8sopXOVBGnBmpGdhl Received: from [177.19.244.154] by web161901.mail.bf1.yahoo.com via HTTP; Tue, 16 Aug 2011 11:40:18 PDT X-Mailer: YahooMailWebService/0.8.113.313619 References: <1313173875.46661.YahooMailNeo@web161913.mail.bf1.yahoo.com> Message-ID: <1313520018.19282.YahooMailNeo@web161901.mail.bf1.yahoo.com> Date: Tue, 16 Aug 2011 11:40:18 -0700 (PDT) From: Nelson Gonzaga Reply-To: Nelson Gonzaga Subject: Re: lo_import/lo_export in other table To: Greg Spiegelberg Cc: pgsql admin In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-78580583-1313520018=:19282" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.886 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.988 X-Spam-Level: X-Archive-Number: 201108/150 X-Sequence-Number: 38137 --0-78580583-1313520018=:19282 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Greg,=0AVery cool your idea, I'll use it in my app.=0AAnd about lo_export, = how can I get the file from this table?=0A=A0=0ATks,=0ANelson=0A=0A=0ADe: G= reg Spiegelberg =0APara: Nelson Gonzaga =0ACc: pgsql admin =0AEnviadas: Ter=E7= a-feira, 16 de Agosto de 2011 9:04=0AAssunto: Re: [ADMIN] lo_import/lo_expo= rt in other table=0A=0A=0AOn Fri, Aug 12, 2011 at 12:31 PM, Nelson Gonzaga = wrote:=0A=0AHi all,=0A>How can I modify (or create oth= er one) lo_import to save data in my table (not in pg_largeobject).=0A>Beca= use I'm making an app that create a=A0table with my fields and a bytea fiel= d, but .net doesn't read that field like lo_export.=0A>=0A>Any ideas?=0A>= =0A>tks in advance,=0A>=A0=0A=0AI have a function that leverages the pg_lar= geobject table to load contents into my own table. =A0The current way, whic= h works well enough for us right now though it's under review, is a functio= n that does this:=0A=0A=A0 SELECT lo_import INTO my_oid FROM lo_import(path= );=0A=A0 my_contents :=3D '';=0A=A0 FOR block IN EXECUTE 'SELECT data FROM = pg_largeobject WHERE loid=3D' || my_oid || ' ORDER BY pageno asc;'=0A=A0 LO= OP=0A=A0 =A0 my_contents :=3D my_contents || block;=0A=A0 END LOOP;=0A=A0 P= ERFORM lo_unlink(my_oid);=0A=A0 INSERT INTO public.contents (hash,bin_conte= nts) VALUES (my_hash,my_contents);=0A=0AThe hash is a text field in our tab= le that today contains a MD5 hash. =A0There are checks in the function to e= nsure no two rows have the same hash to prevent duplication. =A0There's mor= e to it that this but I want to say I've seen it load 2,000+ files averagin= g about 24 KB in under 5 seconds.=0A=0AThere is a pg_readfile(text) functio= n however the text path to the file must be relative to the PGDATA director= y. =A0Didn't want our processes mucking around in there so never gave it mu= ch more thought but it may serve your purposes. =0A=0ABTW, we went this rou= te because INSERTs were taking far too long versus the lo_import() and INSE= RTs also have all their values echo'd to the database log file which slowed= things down.=0A=0AHTH,=0AGreg --0-78580583-1313520018=:19282 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Greg,
Very cool your idea,= I'll use it in my app.
And about lo_export,= how can I get the file from this table?
 
Tks,
Nelson


De: Greg Spiegelberg <gspiegelberg@gmail.com>
Para: Nelson Gonzaga <ngonzaga@yahoo.c= om>
Cc: pgsql admin &= lt;pgsql-admin@postgresql.org>
E= nviadas: Ter=E7a-feira, 16 de Agosto de 2011 9:04
Assunto: Re: [ADMIN] lo_import/lo_expo= rt in other table

On Fri, Aug 12, 2011 at 12:31 PM, Nel= son Gonzaga <ngonzaga@= yahoo.com> wrote:
Hi all,
How can I modify (or create other one) lo_import to save data in my ta= ble (not in pg_largeobject).
Because I'm making an app that create a table with my fields and = a bytea field, but .net doesn't read that field like lo_export.
 
Any ideas?
 
tks in advance,
 

I have a function that leverages the pg_largeobject table to load cont= ents into my own table.  The current way, which works well enough for = us right now though it's under review, is a function that does this:

  SELECT lo_import INTO my_oid FROM lo_import(path);
  my_contents :=3D '';
  FOR block IN EXECUTE 'SELECT data FROM pg_largeobject WHERE loi= d=3D' || my_oid || ' ORDER BY pageno asc;'
  LOOP
    my_contents :=3D my_contents || block;
  END LOOP;
  PERFORM lo_unlink(my_oid);
  INSERT INTO public.contents (hash,bin_contents) VALUES (my_hash= ,my_contents);

The hash is a text field in our table that today contains a MD5 hash. =  There are checks in the function to ensure no two rows have the same = hash to prevent duplication.  There's more to it that this but I want = to say I've seen it load 2,000+ files averaging about 24 KB in under 5 seco= nds.

There is a pg_readfile(text) function however the text path to the fil= e must be relative to the PGDATA directory.  Didn't want our processes= mucking around in there so never gave it much more thought but it may serv= e your purposes.

BTW, we went this route because INSERTs were taking far too long versu= s the lo_import() and INSERTs also have all their values echo'd to the data= base log file which slowed things down.

HTH,
Greg



=
--0-78580583-1313520018=:19282--