Skip site navigation (1) Skip section navigation (2)

Re: bytea columns and memory exhaustion

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: pgsql-php(at)postgresql(dot)org
Cc: tv(at)fuzzy(dot)cz
Subject: Re: bytea columns and memory exhaustion
Date: 2008-05-01 17:30:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-php
On Wednesday 30 April 2008 12:50, tv(at)fuzzy(dot)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 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

In response to


pgsql-php by date

Next:From: Tomas VondraDate: 2008-05-01 17:50:36
Subject: Re: bytea columns and memory exhaustion
Previous:From: tvDate: 2008-04-30 16:50:10
Subject: bytea columns and memory exhaustion

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group