I've found a strange problem when dumping a database with a view that has a
comparison between relative times.
I have a database with a view that was created with the following statement:
CREATE VIEW connected_merchant AS
SELECT merchant_client.merchant_id, merchant_client.version,
merchant_client.last_access_time,
merchant_client.broker_ip_address, merchant_client.broker_port,
merchant_client.merchant_ip_address,
merchant_client.merchant_port, merchant_client.connection_type,
merchant_client.polling_frequency
FROM merchant_client
WHERE merchant_client.connection_type = 'Push'
OR
( merchant_client.connection_type = 'Pull'
AND
CURRENT_TIMESTAMP - merchant_client.last_access_time < 60 );
When I dump the database with pg_dumpall, the dump file rewrites the script to
create the view as the following statement:
CREATE VIEW "connected_merchant" as
SELECT merchant_client.merchant_id, merchant_client."version",
merchant_client.last_access_time, merchant_client.broker_ip_address,
merchant_client.broker_port, merchant_client.merchant_ip_address,
merchant_client.merchant_port, merchant_client.connection_type,
merchant_client.polling_frequency
FROM merchant_client
WHERE ((merchant_client.connection_type = 'Push'::"varchar")
OR
((merchant_client.connection_type = 'Pull'::"varchar")
AND
(reltime(("timestamp"('now'::text) -
merchant_client.last_access_time)) < (60)::reltime)));
The problem is that if I try to restore the database from the dump file I get
the following error:
psql:/tmp/db_2002-05-02.dump:1692: ERROR: Bad reltime external representation
'60'
Anybody knows if this a Postgres bug or if I'm doing something wrong?
Thanks.
--
Juan Jose Comellas
(juanjo@comellas.org)