Re: 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