Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9DE49632C19 for ; Mon, 2 Feb 2009 09:17:44 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 21522-07 for ; Mon, 2 Feb 2009 09:17:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (nathalia.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id 42382632BFA for ; Mon, 2 Feb 2009 09:17:41 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id D6BFC7AC235; Mon, 2 Feb 2009 14:17:38 +0100 (CET) Received: from mail.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id BQdmrSVr7imM; Mon, 2 Feb 2009 14:17:38 +0100 (CET) Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100]) by mail.gransy.com (Postfix) with ESMTP id AE3067AC002; Mon, 2 Feb 2009 14:17:38 +0100 (CET) Received: from sq.gransy.com (localhost [127.0.0.1]) by anastacia.gransy.com (Postfix) with ESMTP id B55A931FD43; Mon, 2 Feb 2009 14:17:38 +0100 (CET) Received: from 193.179.187.70 (proxying for unknown) (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Mon, 2 Feb 2009 14:17:38 +0100 (CET) Message-ID: <5713.193.179.187.70.1233580658.squirrel@sq.gransy.com> In-Reply-To: <200902021355.35100.pgsql@pivert.org> References: <200902021102.06157.pgsql@pivert.org> <38255.62.40.76.70.1233575704.squirrel@sq.gransy.com> <200902021355.35100.pgsql@pivert.org> Date: Mon, 2 Feb 2009 14:17:38 +0100 (CET) Subject: Re: How to store files into the DB with PHP. (bytea ?) From: tv@fuzzy.cz To: =?iso-8859-2?Q?Fran=E7ois_Delpierre?= Cc: pgsql-php@postgresql.org User-Agent: SquirrelMail/1.4.10a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/4 X-Sequence-Number: 3980 >> Choice between BYTEA and LOB is mostly about memory requirements - when >> loading / storing BYTEA columns, you need enough memory to keep the >> whole >> object (data stored in the column). So when constructing the INSERT >> statement you have to place the (escaped) data into it, and that may >> exceed the memory_limit. With LOB objects this is not a problem thanks >> to >> streaming (you may open the LOB as a stream and read / write). > So, there is no way to push binary directly into BYTEA without the need to > escape ? AFAIK no (at least in PHP) - you have to escape the data before using them in SQL statement. > I assume that you're talking about memory_limit parameter of PHP on the > web > server. > Can we still use streaming through a stored procedure ? The LOB principle is quite simple - each LOB is identified by OID, split into multiple segments and stored in a table pg_largeobject. I am just guessing but there are functions used to read / store the LOBs, so the streaming might work (unless you want to use the tickets). But I am just guessing - about a month ago I wrote an article about ways to store files in a PostgreSQL database: http://www.fuzzy.cz/en/articles/storing-files-in-a-postgresql-database/ The site is still under construction, so a lot of stuff is missing there (sorry), but the last section might give you a hint how to mimic LOBs, get rid of some of the BYTEA / LOB disadvantages. >> Sorry, but you have some custom layer of stored procedures, so we may >> only >> guess. But LOBs are identified using an OID, so the procedures might >> return OIDs. > The web server user cannot read (or write) any table directly, so it's > forced > to use a stored procedure to read or write data. So, all the PHP fuctions > on > LOB are useless I guess.