Postgres Pro
Downloads
Home   >   mailing lists

Re: Use of 'now' constant datatype in view to take advantage of partitioned table - Mailing list pgsql-general

From Patrick Dung
Subject Re: Use of 'now' constant datatype in view to take advantage of partitioned table
Date
Msg-id 1408613784.9338.YahooMailNeo@web193506.mail.sg3.yahoo.com
Whole thread Raw
In response to Re: Use of 'now' constant datatype in view to take advantage of partitioned table  (Ken Tanzer <ken.tanzer@gmail.com>)
Responses Re: Use of 'now' constant datatype in view to take advantage of partitioned table  (Alban Hertroys <haramrae@gmail.com>)
List pgsql-general
Tree view
Hi Ken,

Thanks for reply.

1.
The problem is that using 'now' in VIEW, the resulting VIEW will hard code the current timestamp.
It is not dynamic.
If I use write the view like this:

 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::timestamp without time zone
 AND food.post_timestamp <= 'now'::timestamp without time zone

The VIEW will be created like this:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval) AND food.post_timestamp <= '2014-08-21 17:32:21.787179'::timestamp without time zone


2.
now() is dynamic but it scan all the partitioned tables.

Thanks and regards,
Patrick


On Thursday, August 21, 2014 4:27 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:



EXPLAIN ANALYZE
 SELECT *
   FROM food
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND food.post_timestamp <= 'now'
  ORDER BY food.post_timestamp  DESC
 LIMIT 30;


I think the problem is that you're using 'now'::date in your first example, which gets frozen.  You can use now() or current_timestamp or current_date to get dynamic results.

CREATE  VIEW test_now AS SELECT current_timestamp as current_ts, now() as now_function,'now'::timestamp AS now_literal;

(wait a couple of seconds)

SELECT * FROM test_now;

          current_ts           |         now_function          |        now_literal         
-------------------------------+-------------------------------+----------------------------
 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:18:22.207073
(1 row)

You'll see that the last column is frozen while the other two stay current.

Cheers,
Ken



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.


pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
Next
From: Patrick Dung
Date:
Subject: Use of 'now' constant datatype in view to take advantage of partitioned table