Обсуждение: 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