php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33587 pg_query fetches ALL rows, using lots of memory
Submitted: 2005-07-06 13:19 UTC Modified: 2005-07-06 15:53 UTC
From: vda at ilport dot com dot ua Assigned:
Status: Not a bug Package: PostgreSQL related
PHP Version: 4.3.10 OS: Linux
Private report: No CVE-ID: None
 [2005-07-06 13:19 UTC] vda at ilport dot com dot ua
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;
        }
}



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-07-06 13:39 UTC] tony2001@php.net
>I think this is a problem with PostgreSQL client libs.
Please explain: 
you think that this is a problem with PG client libs and you're reporting it in PHP bug system?

 [2005-07-06 13:58 UTC] vda at ilport dot com dot ua
I've seen several vague bug reports here "PHP eats lots
of mem with PostgreSQL" but none was researched in any depth. I thought I can spend at least ten minutes at it.

Also I wanted to have an URL where this bug is reported.
Apparently PostgreSQL team does not have bugzilla.

I reported the issue to PostgreSQL team also
using http://www.postgresql.org/support/submitbug,
repeating bug report and also gave then URL
to this page.

Did I do something wrong?
 [2005-07-06 14:00 UTC] tony2001@php.net
Yes.
PostgreSQL bugs do not belong here, report them to the PG developers instead.
 [2005-07-06 14:01 UTC] vda at ilport dot com dot ua
This is the script which shows the issue nicely.
It reads and immediately discards all rows
(was written as a benchmark "PG versus Oracle").
Table has 200Mb of data in it. Apache's memory
usage was outright silly (~250Mb).

<?

$db = pg_connect("host=172.17.13.22 port=5432 dbname=traffic user=postgres");

/* php bug? sweels enormously while executing this: */
$result = pg_query($db, "SELECT * FROM payment");

$count = 0;

while (pg_fetch_row($result)) {
    $count++;
    if (($count % 1000)==0) echo "$count<br>";
}

echo "Done, count=$count";

?>
 [2005-07-06 14:04 UTC] vda at ilport dot com dot ua
Which part of below text you do not understand?

>Apparently PostgreSQL team does not have bugzilla.
>I reported the issue to PostgreSQL team also
>using http://www.postgresql.org/support/submitbug,
>repeating bug report and also gave then URL
>to this page.
 [2005-07-06 14:19 UTC] tony2001@php.net
PG has mailing list for reporting and discussing PG bugs and you can use it for reporting *their* issues directly to *them*. See http://www.postgresql.org/community/lists/

This is not a support forum or a free bug tracking system that you can use to report any bugs you find in someone's libs.
This system is only for bugs in PHP itself.
As for your issue: this is not PHP problem -> bogus.
 [2005-07-06 15:53 UTC] vda at ilport dot com dot ua
Please be assured that I did not plan to annoy PHP
developers, not at all. I wanted to make this
behaviour and it's root cause known.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Jun 25 08:01:29 2024 UTC