Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 97A982E003A for ; Thu, 1 May 2008 14:31:05 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 87782-05 for ; Thu, 1 May 2008 14:31:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by developer.postgresql.org (Postfix) with ESMTP id D32762E0037 for ; Thu, 1 May 2008 14:31:01 -0300 (ADT) Received: from new-host.home ([72.81.136.83]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0K0700BK89ZCNI71@vms044.mailsrvcs.net> for pgsql-php@postgresql.org; Thu, 01 May 2008 12:30:50 -0500 (CDT) Date: Thu, 01 May 2008 13:30:44 -0400 From: Robert Treat Subject: Re: bytea columns and memory exhaustion In-reply-to: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com> To: pgsql-php@postgresql.org Cc: tv@fuzzy.cz Message-id: <200805011330.44371.xzilla@users.sourceforge.net> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-2 Content-transfer-encoding: 7bit Content-disposition: inline References: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com> User-Agent: KMail/1.9.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/1 X-Sequence-Number: 3843 On Wednesday 30 April 2008 12:50, tv@fuzzy.cz wrote: > Hi all, > > we have a quite nice web application written in PHP over PostgreSQL, with > all the data stored in a PostgreSQL database, including files stored in > BYTEA columns. It worked quite nice for a long time, but the users began > to insert large files (say 1 - 10 megabytes in size) and we began to > experience 'allowed memory exhausted' PHP fatal errors. > > I believe the problem is caused by escaping the bytea data, which > multiplies the amount of required memory, so a 4MB file easily exhausts > the 20MB limit we've set when calling pg_fetch_array(). This is probably > made worse by usage of UTF-8, but not sure about it. > > I guess I'm not the only one here who was hit by this problem, so I'd like > to hear your recommendations how to solve it. I've already thounght about > these solutions: > > 1) Increasing the PHP memory limit > > I'm not a big fan of this, as we would hit the new limit sooner or later, > and we're not the only users of the server. > > 2) Using large objects and pg_lo_* functions instead of BYTEA columns. > > I don't like this, as it would require a serious redesign of the database > schema and the PHP application itself. Moreover according to the user > comments in http://cz.php.net/manual/en/function.pg-lo-open.php it would > require a superuser access to the database, which is not possible in our > case. > > 3) Fetching the BYTEA piece by piece using substring() > > This is the only reliable and 'elegant enough' solution I've found so far. > It lies in fetching the BYTEA column in 'small chunks' (say 10 - 100k in > size), unescaping each of them and either storing it or writing it to the > output before before fetching the next one. I still have to think about > upload / insert, though ... > Just wondering if using cursors might allow you to get around it? -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL