Inconsistent values for 'now'
Hi,
I am trying to setup rules on a view that will maintain an audit trail
of modifications in the real table. Things seem to be going ok but when
I EXPLAIN my queries, the literal 'now' is being given two different
values, one 2 days earlier. I am running 7.4.7
The view is defined to be;
CREATE VIEW domain_registry AS
SELECT *
FROM domain_registry_history
WHERE tstamp > 'now';
The current record always having a tstamp of 'infinity'
admin=# explain update domain_registry set status='N' where
domain='xxxxxx.co.nz';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..21.58 rows=4 width=304)
-> Index Scan using domain_registry_history_pkey on
domain_registry_history (cost=0.00..7.18 rows=2 width=304)
Index Cond: (('xxxxxx.co.nz'::text = ("domain")::text) AND
(tstamp > '2005-04-01 14:39:49.529816'::timestamp without time zone))
-> Index Scan using domain_registry_history_pkey on
domain_registry_history (cost=0.00..7.18 rows=2 width=146)
Index Cond: ((("domain")::text = 'xxxxxx.co.nz'::text) AND
(tstamp > '2005-03-29 15:26:50.361408'::timestamp without time zone))
Index Scan using domain_registry_history_pkey on
domain_registry_history (cost=0.00..7.18 rows=2 width=293)
Index Cond: ((("domain")::text = 'xxxxxx.co.nz'::text) AND (tstamp >
'2005-03-29 15:26:50.361408'::timestamp without time zone))
(8 rows)
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz
"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
Glen Eustace <geustace@godzone.net.nz> writes:
The view is defined to be;
CREATE VIEW domain_registry AS
SELECT *
FROM domain_registry_history
WHERE tstamp > 'now';
Offhand I'd expect the 'now' to be reduced to a timestamp constant
at the time the view is created.
Perhaps you were expecting something like
CREATE VIEW domain_registry AS
SELECT *
FROM domain_registry_history
WHERE tstamp > now();
although personally I'd not feel very comfortable with the idea of a
view whose contents change over time even when you weren't actually
doing anything to the database. Maybe you should rethink this idea
altogether...
regards, tom lane
Offhand I'd expect the 'now' to be reduced to a timestamp constant
at the time the view is created.
Hmmm, my assumption had been that the 'now' constant would be evaluated
everytime the underlying SELECT was build by the planner.
although personally I'd not feel very comfortable with the idea of a
view whose contents change over time even when you weren't actually
doing anything to the database. Maybe you should rethink this idea
altogether...
This technique was one that was recommended to me by an experienced DBA.
Apparently, he and others use it a lot.
Using the function now() rather than the literal 'now' works as
expected. Thanks.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz
"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"