Robert Treat napsal(a):
On Wednesday 30 April 2008 12:50, tv@xxxxxxxx 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
