The following bug has been logged online:

Bug reference: 1756
Logged by: Denis Vlasenko
Email address: vda (AT) ilport (DOT) com.ua
PostgreSQL version: 8.0.1
Operating system: Linux
Description: PQexec eats huge amounts of memory
Details:

Verbatim from http://bugs.php.net/bug.php?id=33587:

Description:
------------
Seen on php-4.3.4RC2. Since I was just testing how good
PG fares compared to Oracle, and I am not feeling any
real pain from this (IOW: not my itch to scratch),
I do not research this in depth, apart from submitting
bug report. Sorry.

Symptom: even the simplest query
$result = pg_query($db, "SELECT * FROM big_table");
eats enormous amounts of memory on server
(proportional to table size).

I think this is a problem with PostgreSQL client libs.
php's source is included for easy reference.

PHP_FUNCTION(pg_query)
{

....
pgsql_result = PQexec(pgsql, Z_STRVAL_PP(query));
if ((PGG(auto_reset_persistent) & 2) && PQstatus(pgsql) !=
CONNECTION_OK) {
PQclear(pgsql_result);
PQreset(pgsql);
pgsql_result = PQexec(pgsql, Z_STRVAL_PP(query));
}

if (pgsql_result) {
status = PQresultStatus(pgsql_result);
} else {
status = (ExecStatusType) PQstatus(pgsql);
}

switch (status) {
case PGRES_EMPTY_QUERY:
case PGRES_BAD_RESPONSE:
case PGRES_NONFATAL_ERROR:
case PGRES_FATAL_ERROR:
php_error_docref(NULL TSRMLS_CC, E_WARNING,
"Query failed: %s.", PQerrorMessage(pgsql));
PQclear(pgsql_result);
RETURN_FALSE;
break;
case PGRES_COMMAND_OK: /* successful command that did
not return rows */
default:
if (pgsql_result) {
pg_result = (pgsql_result_handle *)
emalloc(sizeof(pgsql_result_handle));
pg_result->conn = pgsql;
pg_result->result = pgsql_result;
pg_result->row = 0;
ZEND_REGISTER_RESOURCE(return_value,
pg_result, le_result);
} else {
PQclear(pgsql_result);
RETURN_FALSE;
}
break;
}
}

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

------=_Part_3238_26198124.1120657950846
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Denis,

$result =3D pg_query($db, "SELECT * FROM big_table");
you are reading a big result (as I suspect from big_table) into memory. It=
=20
is perfectly normal that this uses large amounts of memory.

[it would be rather suspicious if loading a big file / big resultset would=
=20
not use big amounts of memory]

Harald


--=20
GHUM Harald Massa
persuasion python postgresql
Harald Armin Massa
Reinsburgstra=DFe 202b
70197 Stuttgart
0173/9409607

------=_Part_3238_26198124.1120657950846
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Denis,<br>
<br>
<div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(=
204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">$result =3D =
pg_query($db, &quot;SELECT * FROM big_table&quot;<br></blockquote></div><=
br>

you are reading a big result (as I suspect from big_table) into memory.
It is perfectly normal that this uses large amounts of memory.<br>
<br>
[it would be rather suspicious if loading a big file / big resultset would =
not use big amounts of memory]<br>
<br>
Harald<br>
<br>
<br>-- <br>GHUM Harald Massa<br>persuasion python postgresql<br>Harald Armi=
n Massa<br>Reinsburgstra=DFe 202b<br>70197 Stuttgart<br>0173/9409607

------=_Part_3238_26198124.1120657950846--

Denis Vlasenko wrote:
Right, which is exactly what you would expect. The entire result set is
sent to the client and stored in local memory; if you only want to
process part of the result set at a time, use a cursor.

(And I'm a little suspicious that the performance of "SELECT * FROM
big_table" will contribute to a meaningful comparison between database
systems.)

-Neil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Denis Vlasenko wrote:
Perhaps; presumably Oracle is essentially creating a cursor for you
behind the scenes. libpq does not attempt to do this automatically; if
you need a cursor, you can create one by hand.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wednesday 06 July 2005 16:52, Harald Armin Massa wrote:
No, I am not reading it into memory. I am executing query _on the server_,
fetching result row-by-row and discarding rows as prey are processed
(i.e. without accumulating all rows in _client's memory_) in the part
of php script which you snipped off.

Similar construct with Oracle, with 10x larger table,
does not use Apache (php) memory significantly.

php's pg_query() calls PQuery(), a Postgresql client library function,
which is likely implemented so that it fetches all rows and stores them
in client's RAM before completion.

Oracle OCI8 does not work this way, it keeps result set
on db server (in a form of a cursor or something like that).

vda


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Thursday 07 July 2005 08:54, Neil Conway wrote:
The same php script but done against Oracle does not have this
behaviour.

I wanted to show colleagues which are Oracle admins that peak
data fetch rate of PostgreSQL is way better than Oracle one.

While it turned out to be true (Oracle+WinNT = 2kb TCP output buffer,
~1Mb/s over 100Mbit; PostgreSQL+Linux = 8kb buffer, ~2.6Mb/s),
I was ridiculed instead when my php script failed miserably,
crashing Apache with OOM condition, while alanogous script for Oracle
ran to completion just fine.
--
vda


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, Jul 07, 2005 at 09:51:54AM +0300, Denis Vlasenko wrote:

You should have tested the script before showing off :-) You may want
to convert it to manually use a cursor, at least the Postgres version.
That would alleviate the memory problem.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Some men are heterosexual, and some are bisexual, and some
men don't think about sex at all... they become lawyers" (Woody Allen)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Neil Conway wrote:
I do not understand how a cursor could be autocreated by a query like

$result = pg_query($db, "SELECT * FROM big_table");

php will expect $result to contain the entire table (yuck!).



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Thu, Jul 07, 2005 at 08:17:23AM -0700, John R Pierce wrote:
Really? I thought what really happened is you had to get the results
one at a time using the pg_fetch family of functions. If that is true,
then it's possible to make the driver fake having the whole table by
using a cursor. (Even if PHP doesn't do it, it's possible for OCI to do
it behind the scenes.)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hi,

A similar topic has been discussed before on pgsql-sql mailing list:
Subject: SELECT very slow Thomas Kellerer
URL: http://archives.postgresql.org/pgsql...6/msg00118.php

Regards.

On 7/6/05, Denis Vlasenko <vda (AT) ilport (DOT) com.ua> wrote:
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly