Thread: Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
Hi All I recently changed hosts for my PHP/PostgreSQL site and have been seeing alot of errors in the errors logs and also some on the site. [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not exist: timestamp without time zone > time without time zone at character 14\nHINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.\nQUERY: SELECT $1 > $2 \nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in /..../include/database.php on line 40[/quote] [quote]PHP Warning: pg_fetch_array() expects parameter 1 to be resource, boolean given in /..../include/common.php on line 402[/quote] [quote]PHP Warning: pg_query(): Query failed: ERROR: function pg_catalog.btrim(bigint) does not exist at character 62\nHINT: No function matches the given name and argument types. You might need to add explicit type casts. in /..../include/database.php on line 40[/quote] [quote]PHP Notice: Undefined index: HTTP_REFERER in ..../include/common.php on line 483[/quote] Does anyone know what might be causing this? Here are the relevant lines from the code: [B]database.php on line 40[/B] [quote]$l_hResult = pg_query($this->m_pHandle, $i_sQuery);[/quote] [B]common.php on line 402[/B] [quote]while ($l_asRow = pg_fetch_array($l_hResult)) {[/quote] [B]common.php on line 483[/B] [quote]$l_sReferer = isset($_POST['referer']) ? trim($_POST['referer']) : base64_encode($_SERVER['HTTP_REFERER']);[/quote] Thank you! -- View this message in context: http://www.nabble.com/Changed-Hosts%2C-Lots-of-Errors-in-PostgreSQL---Help-Please%21-tp16769300p16769300.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
BLazeD wrote: > [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not > exist: timestamp without time zone > time without time zone That's really odd. I can't imagine why the timestamp > timestamp operator might be absent. What's the output of the command "select version()" on your new host? If you connect to your database with the psql command line tool and run the command: \do > does an entry with both operand types `timestamp without time zone' appear? If you don't have direct access using psql, you can wrap the following query up in a bit of PHP and see what the result of it is instead: select * from pg_catalog.pg_operator where oprcode = 'timestamp_gt'::regproc; > at character > 14\nHINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts.\nQUERY: SELECT $1 > $2 > \nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in > /..../include/database.php on line 40[/quote] > OK, so it's inside PL/PgSQL. Do you have the same problem if you execute a similar query manually, like: select current_timestamp :: timestamp without time zone > current_timestamp :: timestamp without time zone; ? If not, try reloading your stored procedures and see if you still have problems. If you still have issues, wrap a simple test statement up in a PL/PgSQL stored procedure and see if it executes correctly, eg: CREATE OR REPLACE FUNCTION testop () RETURNS boolean AS $$ BEGIN return current_timestamp :: timestamp without time zone > current_timestamp :: timestamp without time zone; END; $$ LANGUAGE 'plpgsql'; ... though I cannot imagine why it might work as a standalone statement but not in a stored procedure. If you want more help I suggest posting the actual SQL statements that are causing problems. If possible get them from the server logs after enabling statement logging, or from the pg interface in PHP if it has any statement logging features. -- Craig Ringer
On Friday 18 April 2008 8:27 pm, BLazeD wrote: > Hi All > > I recently changed hosts for my PHP/PostgreSQL site and have been seeing > alot of errors in the errors logs and also some on the site. > > [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not > exist: timestamp without time zone > time without time zone at character > 14\nHINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts.\nQUERY: SELECT $1 > $2 > \nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in > /..../include/database.php on line 40[/quote] > > [quote]PHP Warning: pg_fetch_array() expects parameter 1 to be resource, > boolean given in /..../include/common.php on line 402[/quote] > > [quote]PHP Warning: pg_query(): Query failed: ERROR: function > pg_catalog.btrim(bigint) does not exist at character 62\nHINT: No function > matches the given name and argument types. You might need to add explicit > type casts. in /..../include/database.php on line 40[/quote] > > [quote]PHP Notice: Undefined index: HTTP_REFERER in > ..../include/common.php on line 483[/quote] > > Does anyone know what might be causing this? Here are the relevant lines > from the code: > > [B]database.php on line 40[/B] > > [quote]$l_hResult = pg_query($this->m_pHandle, $i_sQuery);[/quote] > > [B]common.php on line 402[/B] > > [quote]while ($l_asRow = pg_fetch_array($l_hResult)) {[/quote] > > [B]common.php on line 483[/B] > > [quote]$l_sReferer = isset($_POST['referer']) ? > trim($_POST['referer']) > > : base64_encode($_SERVER['HTTP_REFERER']);[/quote] > > Thank you! Did the Postgres versions change from one host to another? -- Adrian Klaver aklaver@comcast.net
BLazeD <gibbasanti@hotmail.com> writes: > [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not > exist: timestamp without time zone > time without time zone at character > 14 Well, it's quite right, there is no such operator. PG 8.3 complains about this, whereas previous versions would have silently converted both operands to text and done a textual comparison ... leading to results that are highly unlikely to be sane at all, for this combination of datatypes. I'd say 8.3 just found a bug in your app for you. > [quote]PHP Warning: pg_query(): Query failed: ERROR: function > pg_catalog.btrim(bigint) does not exist at character 62 Again, this is 8.3 being more picky about implicit casts than prior versions. Why would you think btrim on an integer value would be useful, anyway? If you really want it, insert an explicit cast to text. regards, tom lane
At 11:27p -0400 on Fri, 18 Apr 2008, BLazeD wrote: > [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not > exist: timestamp without time zone > time without time zone at character > 14\nHINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts.\nQUERY: SELECT $1 > $2 ^^^^^^^^^^^^^^^^^^^^^^^ It's been awhile since I've messed with timestamps et al, but I don't recall direct operators between timestamp and time. In the host change, did you also get a Postgres upgrade? To 8.3 perhaps, that removed a lot of explicit type casts? > [other errors] If that's it, I'll bet fixing the first error will fix the rest, or at least point you in the right direction. Kevin
Craig Ringer wrote: > BLazeD wrote: >> [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not >> exist: timestamp without time zone > time without time zone > That's really odd. I can't imagine why the timestamp > timestamp > operator might be absent. > Hmm.... I managed to read that error repeatedly and somehow STILL miss `time' vs `timestamp'. Please disregard my reply, as it's of no use due to that misreading. -- Craig Ringer
Hey there Yup it went from 8.1.9 to 8.3 Adrian Klaver wrote: > > On Friday 18 April 2008 8:27 pm, BLazeD wrote: >> Hi All >> >> I recently changed hosts for my PHP/PostgreSQL site and have been seeing >> alot of errors in the errors logs and also some on the site. >> >> [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not >> exist: timestamp without time zone > time without time zone at character >> 14\nHINT: No operator matches the given name and argument type(s). You >> might need to add explicit type casts.\nQUERY: SELECT $1 > $2 >> \nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in >> /..../include/database.php on line 40[/quote] >> >> [quote]PHP Warning: pg_fetch_array() expects parameter 1 to be resource, >> boolean given in /..../include/common.php on line 402[/quote] >> >> [quote]PHP Warning: pg_query(): Query failed: ERROR: function >> pg_catalog.btrim(bigint) does not exist at character 62\nHINT: No >> function >> matches the given name and argument types. You might need to add explicit >> type casts. in /..../include/database.php on line 40[/quote] >> >> [quote]PHP Notice: Undefined index: HTTP_REFERER in >> ..../include/common.php on line 483[/quote] >> >> Does anyone know what might be causing this? Here are the relevant lines >> from the code: >> >> [B]database.php on line 40[/B] >> >> [quote]$l_hResult = pg_query($this->m_pHandle, $i_sQuery);[/quote] >> >> [B]common.php on line 402[/B] >> >> [quote]while ($l_asRow = pg_fetch_array($l_hResult)) {[/quote] >> >> [B]common.php on line 483[/B] >> >> [quote]$l_sReferer = isset($_POST['referer']) ? >> trim($_POST['referer']) >> >> : base64_encode($_SERVER['HTTP_REFERER']);[/quote] >> >> Thank you! > > Did the Postgres versions change from one host to another? > > -- > Adrian Klaver > aklaver@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Changed-Hosts%2C-Lots-of-Errors-in-PostgreSQL---Help-Please%21-tp16769300p16797840.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.