Thread: 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"