Обсуждение: PostgreSQL + PHP 4.2x buggy with Apache?

Hi,

anyone that could help me out would do me a great favour.

I ran in to this problem yesterday.
Issue: I am trying to import a huge table into pgsql using "Copy
from".
I do it like this: Simple html-interface, opening txt-files
and then I use copy to perform a high-speed insert.

The environment used from the beginning was Apache 2.0.39 + PHP 4.2.2
+ PostgreSQL 7.2 under Cygwin.

After a while I noticed that the webserver stopped responding so I
downgraded to 1.3.26 and now everything started to jump fairly well,
until yesterday night.

I had just processed a test file of 200 000 records. (I issue an "end
copy" command every 1000 lines in some sort of "Commit".
Commit example:
    pg_put_line($link, "\\.\n");
    pg_end_copy($link);
    $query = "COPY test FROM stdin;";
    $sth = pg_query ($link, $query);

After last night I cannot connect via the webinterface anylonger. The
webserver still answers on requests and I can logon via psql or
pgAdmin and issue commands there, but no way to connect via my
PHP-script.

the line that hangs is the pg_connect:
$link = pg_pconnect("dbname=$Database user=$DbId password=$DbPassword
host=localhost");

    $query = "COPY test FROM stdin;";
    $sth = pg_query ($link, $query);

regards, Axier

It sounds like you have a problem with the persistent PostgreSQL
connections used by PHP and Apache.  Change pg_pconnect to pg_connect and
restart Apache to close the existing persistent connections.  My experience
is that pg_pconnect is not as robust as pg_connect and not worth the
increase in speed.

George Essig

> After last night I cannot connect via the webinterface anylonger. The
> webserver still answers on requests and I can logon via psql or
> pgAdmin and issue commands there, but no way to connect via my
> PHP-script.
>
> the line that hangs is the pg_connect:
> $link = pg_pconnect("dbname=$Database user=$DbId password=$DbPassword
> host=localhost");
>
>          $query = "COPY test FROM stdin;";
>          $sth = pg_query ($link, $query);
>
> regards, Axier

In PHP, you might also want to add 'set_time_limit(0);' to make sure your
PHP script runs until completion.

George Essig


----- Forwarded by George T Essig/STLS/FRS on 08/06/02 02:06 PM -----

                    George T
                    Essig                To:     pgsql-general@postgresql.org
                                         cc:
                    08/06/02             Subject:     Re: PostgreSQL + PHP 4.2x buggy with Apache?
                    01:46 PM





It sounds like you have a problem with the persistent PostgreSQL
connections used by PHP and Apache.  Change pg_pconnect to pg_connect and
restart Apache to close the existing persistent connections.  My experience
is that pg_pconnect is not as robust as pg_connect and not worth the
increase in speed.

George Essig

> After last night I cannot connect via the webinterface anylonger. The
> webserver still answers on requests and I can logon via psql or
> pgAdmin and issue commands there, but no way to connect via my
> PHP-script.
>
> the line that hangs is the pg_connect:
> $link = pg_pconnect("dbname=$Database user=$DbId password=$DbPassword
> host=localhost");
>
>          $query = "COPY test FROM stdin;";
>          $sth = pg_query ($link, $query);
>
> regards, Axier

On Tuesday 06 Aug 2002 3:53 pm, Axier wrote:
> Hi,
>
> anyone that could help me out would do me a great favour.
>
> I ran in to this problem yesterday.
> Issue: I am trying to import a huge table into pgsql using "Copy
> from".
> I do it like this: Simple html-interface, opening txt-files
> and then I use copy to perform a high-speed insert.
>
> The environment used from the beginning was Apache 2.0.39 + PHP 4.2.2
> + PostgreSQL 7.2 under Cygwin.

I'm not sure Cygwin is recommended for a production system - I run on Linux so
I can't say definitely though.

> After a while I noticed that the webserver stopped responding so I
> downgraded to 1.3.26 and now everything started to jump fairly well,
> until yesterday night.

> After last night I cannot connect via the webinterface anylonger. The
> webserver still answers on requests and I can logon via psql or
> pgAdmin and issue commands there, but no way to connect via my
> PHP-script.
>
> the line that hangs is the pg_connect:
> $link = pg_pconnect("dbname=$Database user=$DbId password=$DbPassword
> host=localhost");

Does the pg_pconnect() just hang, or do you get an error code? If you get an
error, then that's something it would be useful to see.

I'm guessing you've got a bunch of connections still open to the database and
that's causing your problems. Use ps to see what's running (if Cygwin handles
that). See if pg_connect() helps perhaps.

I wasn't aware that PHP + Apache 2 was stable yet - stick with 1.3 for the
moment if I was you. Also, I don't think the Postgresql libraries are
thread-safe (not sure though) and I'm not sure if Windows versions of
Apache/PHP use threads by default.

Since you can connect via psql/pgadmin it doesn't look like a problem at the
Postgresql end.

- Richard Huxton

> The environment used from the beginning was Apache 2.0.39 + PHP 4.2.2
> + PostgreSQL 7.2 under Cygwin.

I tried and played with Apache 2.0.39, PHP 4.2.2 and postgreSQL 7.2.1 - to
no avail. Sometimes, PHP scripts worked, reloaded and they failed,
sometimes just PHP stopped to interpret and just delivered source or
wasn't able to connect to postgres any more.

Downgrading Apache to 1.3.26 immediately solved all problem. 'kay, have to
admit, that 2.0.39 + 4.2.2 is not stable yet! So no "real problem".


Kind regards
... Ralph ...

Than you guys... I made a goofy, not noticing that I used the persistent
variant of pg_connect and I would probably not have discovered it.

I run Apache 1.3.26 and can agree on it's higher stability,
at least when running psql under the cygwin platform.

/ Axier

maillist@shauny.de (Ralph Graulich) wrote in message
news:<Pine.LNX.4.21.0208062133350.25616-100000@shauny.shauny.de>...
> > The environment used from the beginning was Apache 2.0.39 + PHP 4.2.2
> > + PostgreSQL 7.2 under Cygwin.
>
> I tried and played with Apache 2.0.39, PHP 4.2.2 and postgreSQL 7.2.1 - to
> no avail. Sometimes, PHP scripts worked, reloaded and they failed,
> sometimes just PHP stopped to interpret and just delivered source or
> wasn't able to connect to postgres any more.
>
> Downgrading Apache to 1.3.26 immediately solved all problem. 'kay, have to
> admit, that 2.0.39 + 4.2.2 is not stable yet! So no "real problem".
>
>
> Kind regards
> ... Ralph ...
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org