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

Поиск
Список
Период
Сортировка
От Patrick Dung
Тема Use of 'now' constant datatype in view to take advantage of partitioned table
Дата
Msg-id 1408599794.24265.YahooMailNeo@web193505.mail.sg3.yahoo.com
обсуждение исходный текст
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Patrick Dung
Дата:
Сообщение: Re: Use of 'now' constant datatype in view to take advantage of partitioned table
Следующее
От: Steve Kehlet
Дата:
Сообщение: Re: hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?