Changed Hosts, Lots of Errors in PostgreSQL - Help Please!

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

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.

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