Re: Recurring events

On 06/06/2011 06:59 PM, Thomas Guettler wrote:
> Hi,
>
> how do you store recurring events in a database?

I use two tables: one table that stores the recurring event, and another
that's essentially a materialized view containing instances of the event.

It's not ideal, but performs better than using generate_series to
produce and filter the event series on the fly.

> end_datetime can be NULL (open end).

PostgreSQL has an ideal solution to this: the timestamp value
'infinite'. Using it dramatically simplified my interval-related
queries. I initially used it in my design, only to discover that JDBC
doesn't support infinite dates (argh!) and neither do many languages. I
find this exceptionally frustrating.

> Can you create an indexed view with infinite rows? I only want to index
> the last three year and the next three years.

You essentially need to generate a materialized view and maintain it
using triggers on the main table to insert/update/delete rows in the
materialized view when the original representation changes.

The serialized table is read-only to apps; they cannot alter it
directly. It's updated only via SECURITY DEFINER triggers on the table
that stores the recurring event ranges. That helps make it clear that
it's a materialized view not a "real" table.

Another approach to this problem, btw, is to query your intervals table
and select the date range(s) that match certain queries using BETWEEN
queries. Using 'null' for open intervals makes this ugly because you
need lots of CASE statements, but it works. Once you have matched a set
of date ranges, you *then* use generate_series to output individual
instances on the fly. This can be a lot more efficient than querying a
materialized view if you tend to only be interested in narrow date
ranges or single days/weeks. It performs less well when you want to
generate a result set containing every day over a two year period. It
can be a good approach depending on the kind of queries you expect to
need to run. You can always do both: have a materialized view, but
writes some queries that ignore it and select from the intervals table
directly then generate result rows on the fly.

--
Craig Ringer