Thread: Wierd memory problem with Apache / PHP. Related to pg_query ?

Hi all,

I got a problem with uploading files which encounter the memory limit
when their size is not even close to the memory limit itself, let me
explain.

Here is my code that i made for testing the problem (along the code i
echoed the function memory_get_usage() to know how much memory was
allocated already for the script):

$imagefile    =    $_FILES['imagefile']; // recieve the file
echo memory_get_usage().'<br />';    // 118592 memory bytes allocated

$data = pg_escape_bytea(`cat $imagefile[tmp_name]`);
echo memory_get_usage().'<br />';    // 5570280 memory bytes allocated

$data = "INSERT INTO test_files (bin_data, filename, filesize, filetype)
    VALUES ('$data', '$imagefile[name]', '$imagefile[size]',
'$imagefile[type]')";   // creating the sql for the insert, i called the
received value also $data cause i dont want to keep the previous $data
(after all we want to our precious memory no? :))
echo memory_get_usage().'<br />';    // 5570400 memory bytes allocated
{changed from b4 only alittle}

if ( !$res = pg_query ($this->conn, $data) )  // try to insert the sql
string
    return 'error';
else
    return 'gr8';
echo memory_get_usage().'<br />';    // 5570648 memory bytes allocated
{again changed only alittle}


Now as far as i see the script needed about 5.5MB of memory to upload a
file of 4.7MB but thats what so weird here... i receive the memory limit
error even if the php.ini "memory_limit" is set to 16MB! {twice of the
default of 8MB!}  at 32MB it works fine... but thats way too much..
I suspect that the problem is connected to the pg_query function itself
but i didnt find what made it exactly...

Any ideas, knowledge or even just solutions ;) will be extremly helpful.

Thanks in advance,
Ben-Nes Yonatan

It sounds like a php issue to me more than anythong else. Perhaps PHP's
garbage collection doesn't free the space fast enough?

However, I was looking at the PG source a little to see if it looked
like it was copying the query, and it appears that pqPutMsgBytes (in
fe-misc.c) copies the entire query to a buffer. That could account for
doubling of the query that you send there (since php still holds the
$data string), but still doesn't explain 16MB. Perhaps a little of both
PHP and postgres?

Developers: am I mistaken about libpq copying the entire query before
sending it to the backend? Is there a reason that libpq wouldn't just
send it along to the backend? After all, it seems strange that postgres
would allow a huge query length, yet expect to be able to copy the
entire thing.

Regards,
    Jeff Davis



On Wed, 2005-02-02 at 08:58 +0300, Michael Ben-Nes wrote:
> Hi all,
>
> I got a problem with uploading files which encounter the memory limit
> when their size is not even close to the memory limit itself, let me
> explain.
>
> Here is my code that i made for testing the problem (along the code i
> echoed the function memory_get_usage() to know how much memory was
> allocated already for the script):
>
> $imagefile    =    $_FILES['imagefile']; // recieve the file
> echo memory_get_usage().'<br />';    // 118592 memory bytes allocated
>
> $data = pg_escape_bytea(`cat $imagefile[tmp_name]`);
> echo memory_get_usage().'<br />';    // 5570280 memory bytes allocated
>
> $data = "INSERT INTO test_files (bin_data, filename, filesize, filetype)
>     VALUES ('$data', '$imagefile[name]', '$imagefile[size]',
> '$imagefile[type]')";   // creating the sql for the insert, i called the
> received value also $data cause i dont want to keep the previous $data
> (after all we want to our precious memory no? :))
> echo memory_get_usage().'<br />';    // 5570400 memory bytes allocated
> {changed from b4 only alittle}
>
> if ( !$res = pg_query ($this->conn, $data) )  // try to insert the sql
> string
>     return 'error';
> else
>     return 'gr8';
> echo memory_get_usage().'<br />';    // 5570648 memory bytes allocated
> {again changed only alittle}
>
>
> Now as far as i see the script needed about 5.5MB of memory to upload a
> file of 4.7MB but thats what so weird here... i receive the memory limit
> error even if the php.ini "memory_limit" is set to 16MB! {twice of the
> default of 8MB!}  at 32MB it works fine... but thats way too much..
> I suspect that the problem is connected to the pg_query function itself
> but i didnt find what made it exactly...
>
> Any ideas, knowledge or even just solutions ;) will be extremly helpful.
>
> Thanks in advance,
> Ben-Nes Yonatan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Jeff Davis <jdavis-pgsql@empires.org> writes:
> Developers: am I mistaken about libpq copying the entire query before
> sending it to the backend? Is there a reason that libpq wouldn't just
> send it along to the backend?

That's a feature, not a bug.  libpq marshals whole messages before
sending them so that it can be sure it doesn't have a problem with
a half-sent message.  The only way out of such a problem would be to
drop the connection, because there's no way to regain message boundary
sync with the backend.

If your SQL queries are so long as to pose a memory threat by
themselves, you might want to rethink your approach anyway.  I'd
expect such things to hit bottlenecks all over the place.  In particular
the backend is quite certain to make multiple copies of any long literal
constant during parsing/planning.

            regards, tom lane