On Jun 27, 2011, at 12:36 PM, Christopher Browne wrote:
> I wrote something on this on pgsql-general about 5 years ago that
> still seems pretty relevant.
>
> http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php
iwantsandy.com (now defunct) originally had a solution like this. However it supported a slew of recurrences:
* hours
* 2xday
* days
* weeks
* months
* quarters
* years
* decades
We had materializations of all of these going out 5 years or so. It took up an incredible amount of database space and
wasreally slow. I replaced it with a variation on the code described in this blog post:
http://www.justatheory.com/computers/databases/postgresql/recurring_events.html
The database was a fraction of the original size and, because views were usually limited to a month at most, the number
ofrows generated for a query to show recurring events was quite limited (no one had an hourly reminder that when for
morethan a couple of days). Queries were a lot faster, too.
So I think the materialization of dates can work in certain limited cases such as your "vacations 2005" example, and
willbe easier to use thanks to JOINs, I found that it performed poorly and was unnecessarily resource-intensive for our
usage.And I suspect the same would be try for anyone building a calendar app with more than one simple kind of limited
recurrence.
Best,
David
