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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
