Обсуждение: phpinfo(): postgresql versions' mismatch
Dear all, I have recently found out that PHP libpq Version reported by phpinfo() doesn't match the Actual PGSQL Version on the server. I suspect this is the reason why some of postgresql queries do not run via web interface and run without any problem via psql. I have checked the web for an answer but found so far only this thread: http://svr5.postgresql.org/pgsql-novice/2006-03/msg00208.php They say that it is not critical if php uses the old libpq, still it is better if the versions match. The question is how what shall I do to make them match. I'm using Debian Sarge 3.1 (stable) PostgreSql 8.1.4 (installed via Debian backports) php version 5.2.0-8 (also installed from backports) phpinfo() reports that: PostgreSQL(libpq) Version 7.4.7 I believe I have both libpq3 and libpq4 I've checked /etc/php5/apache2/conf.d/pgsql.ini and /etc/php5/apache2/conf.d/pdo_pgsql.ini /etc/php5/apache2/conf.d/pgsql.ini: extension=pgsql.so /etc/php5/apache2/conf.d/pdo_pgsql.ini: extension=pdo_pgsql.so Thanks in advence for any advice, Mihail
On Fri, Feb 09, 2007 at 11:31:29AM +0200, Mihail Mihailov wrote: > I have recently found out that PHP libpq Version reported by > phpinfo() doesn't match the Actual PGSQL Version on the server. > I suspect this is the reason why some of postgresql queries do not run > via web interface and run without any problem via psql. Only some PHP queries don't run? What's different about those that run and those that don't? Do the queries that don't run fail with an error message? If so, what's the exact message? What do the PostgreSQL logs show for the failed queries? You might need to adjust settings like log_min_error_statement in postgresql.conf to get useful log entries. If you ever upgrade PHP to use 8.x libraries instead of 7.4 libraries, beware that some prepared queries that used to work might start failing. I've seen that happen because PDO emulates prepared queries when linked against 7.4 and uses real prepared queries when linked against 8.x (on Unix-like platforms anyway; I think PDO still emulates prepared queries Windows). The problems arose when the emulated queries made implicit type casts that were no longer made with the real prepared queries. A specific example I saw involved casting a boolean value to an integer: the emulated prepared query converted true and false to 1 and 0, respectively, but the real prepared query passed 't' and 'f' to the backend, which rejected the query with a syntax error because 't' and 'f' aren't integers. -- Michael Fuhr
Hi Michael, Thanks for comments! > On Fri, Feb 09, 2007 at 11:31:29AM +0200, Mihail Mihailov wrote: >> I have recently found out that PHP libpq Version reported by >> phpinfo() doesn't match the Actual PGSQL Version on the server. >> I suspect this is the reason why some of postgresql queries do not run >> via web interface and run without any problem via psql. > > Only some PHP queries don't run? What's different about those that > run and those that don't? At first everything seemed to work fine. Connection works, queries run. So far I have found only one query, which worked in postgres 7.4 and doesn't seem to work now. It is a SELECT query with LIMIT and OFFSET clauses. The first query with OFFSET 0 worked, but the second with OFFSET N returned an empty recordset. I suspected that it has to do with php because the same query works fine in psql environment. > Do the queries that don't run fail with > an error message? If so, what's the exact message? What do the > PostgreSQL logs show for the failed queries? You might need to > adjust settings like log_min_error_statement in postgresql.conf to > get useful log entries. Actually, these queries do not fail they just don't return any data. Do you think it's more likely a bug in the script? > If you ever upgrade PHP to use 8.x libraries instead of 7.4 libraries, > beware that some prepared queries that used to work might start > failing. So, you think it is not a good idea to make php use the 8.x libpq? Is the 7.4 libpq quite compatible with postgres 8.1? Does it affect the speed? Mihail Mihailov
Mihail.Mihailov@uta.fi wrote: > Dear all, > > I have recently found out that PHP libpq Version reported by > phpinfo() doesn't match the Actual PGSQL Version on the server. >... phpinfo() reports the version of libpq which was present when PHP (or the PHP pgsql module) was compiled. It does not reflect runtime. It is best to both compile and use the libpq version that matches the server.
On Fri, Feb 09, 2007 at 07:53:46PM +0200, Mihail Mihailov wrote: > > Only some PHP queries don't run? What's different about those that > > run and those that don't? > > At first everything seemed to work fine. Connection works, queries run. > So far I have found only one query, which worked in postgres 7.4 and > doesn't seem to work now. It is a SELECT query with LIMIT and OFFSET > clauses. > The first query with OFFSET 0 worked, but the second with OFFSET N > returned an empty recordset. > I suspected that it has to do with php because the same query works > fine in psql environment. Have you turned on statement logging in the database to verify that PHP is sending the query you think it is? Have you looked for errors in the database logs? What does the PHP code that fetches the results look like? > Actually, these queries do not fail they just don't return any data. > Do you think it's more likely a bug in the script? Impossible to say without more information. A self-contained test case might be useful. > > If you ever upgrade PHP to use 8.x libraries instead of 7.4 libraries, > > beware that some prepared queries that used to work might start > > failing. > > So, you think it is not a good idea to make php use the 8.x libpq? I didn't mean to suggest not using libpq 8.x; I was suggesting that you review your queries to make sure you're not depending on implicit type casts that PDO's emulated prepared statements might be making that will no longer be made with the "real" prepared statements that PDO uses when linked against libpq 8.x. -- Michael Fuhr
I've checked the script. It was a bug in the script. Now all the queries work fine. Sorry for disturbing. Mihail Quoting Michael Fuhr <mike@fuhr.org>: > On Fri, Feb 09, 2007 at 07:53:46PM +0200, Mihail Mihailov wrote: >> > Only some PHP queries don't run? What's different about those that >> > run and those that don't? >> >> At first everything seemed to work fine. Connection works, queries run. >> So far I have found only one query, which worked in postgres 7.4 and >> doesn't seem to work now. It is a SELECT query with LIMIT and OFFSET >> clauses. >> The first query with OFFSET 0 worked, but the second with OFFSET N >> returned an empty recordset. >> I suspected that it has to do with php because the same query works >> fine in psql environment. > > Have you turned on statement logging in the database to verify that > PHP is sending the query you think it is? Have you looked for > errors in the database logs? What does the PHP code that fetches > the results look like? > >> Actually, these queries do not fail they just don't return any data. >> Do you think it's more likely a bug in the script? > > Impossible to say without more information. A self-contained test > case might be useful. > >> > If you ever upgrade PHP to use 8.x libraries instead of 7.4 libraries, >> > beware that some prepared queries that used to work might start >> > failing. >> >> So, you think it is not a good idea to make php use the 8.x libpq? > > I didn't mean to suggest not using libpq 8.x; I was suggesting that > you review your queries to make sure you're not depending on implicit > type casts that PDO's emulated prepared statements might be making > that will no longer be made with the "real" prepared statements > that PDO uses when linked against libpq 8.x. > > -- > Michael Fuhr > -- Mihail Mihailov, lehtori Käännöstiede (venäjä) Kieli- ja käännöstieteiden laitos 33014 Tampereen yliopisto puh. (03) 3551 6123 sähköposti: mihail.mihailov@uta.fi