Re: Use of 'now' constant datatype in view to take advantage of partitioned table

[Patrick Dung <patrick_dkt@xxxxxxxxxxxx>]

An update, inspired by Date LastDay - PostgreSQL wiki

		Date LastDay - PostgreSQL wiki Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL Depends on Nothing by Scott Bailey 'Artacus'
		View on wiki.postgresql.org	Preview by Yahoo

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

2. Create my View like this:

CREATE OR REPLACE VIEW v_recent_items AS
 SELECT *
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 mon'::interval)::timestamp without time zone AND food.post_timestamp <= now()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;

While running 'EXPLAIN ANALYZE select * from v_recent_items'

I found that it skipped the partition table food_2013p.

Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,

Patrick

On Thursday, August 21, 2014 3:21 PM, Patrick Dung <patrick_dkt@xxxxxxxxxxxx> wrote:

Resent. As I could not see my mail in the mailing list after about two hours.

On Thursday, August 21, 2014 1:43 PM, Patrick Dung <patrick_dkt@xxxxxxxxxxxx> wrote:

Hi Postgresql users,

I have a master table with two partition table (food_2013p, food_2014p).

I found that when I use SELECT + 'now' constant, constraint exclusion works, (it skipped the 2013 partition).

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;

But when I put the query inside view, 'now' is converted to the timestamp when I run the create view.

So the view becomes:

  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date AND food.post_timestamp <= '2014-08-21 13:38:29.642347'::timestamp without time zone

This is not dynamic.

When I use now(), the query will scan other partition tables. I know this is a restriction of partition on non-immutable function.

Would it be possible or a feature request to take advantage of the partition table with query like this?

Thanks and regards,

Patrick