From: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-php@postgresql.org
Subject: Re: bytea columns and memory exhaustion
Date: Thu, 01 May 2008 19:50:36 +0200
Message-ID: <481A02EC.9030600@fuzzy.cz> (raw)
In-Reply-To: <200805011330.44371.xzilla@users.sourceforge.net>
References: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com>
<200805011330.44371.xzilla@users.sourceforge.net>
Robert Treat napsal(a):
> 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?
Not sure how this might fix it. The problem is that once you fetch the
data into PHP (does not matter if it's from cursor or directly from a
SELECT query) the bytea value is too large (due to escaping).
I've tried to implement the SUBSTRING(...) solution described in point
(3) above, and it works quite nice ...
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: bytea columns and memory exhaustion
In-Reply-To: <481A02EC.9030600@fuzzy.cz>
References: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com> <200805011330.44371.xzilla@users.sourceforge.net> <481A02EC.9030600@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox