Re: Recurring events - Mailing list pgsql-general

From Thomas Guettler
Subject Re: Recurring events
Date
Msg-id 4DEE34D8.4060405@tbz-pariv.de
Whole thread Raw
In response to Re: Recurring events  (Craig Ringer <craig@postnewspapers.com.au>)
List pgsql-general
Hi Craig and mailing list

On 07.06.2011 00:54, Craig Ringer wrote:
> 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.

I use Python (and Django ORM) to access Postgres. Infinite is not supported. But this
is no problem.

I look at "Materialized Views" in the wiki:
http://wiki.postgresql.org/wiki/Materialized_Views

The view gets updated by a trigger. But if the date is infinite, you
need to constrain the trigger to the next N years. I guess this is the
best solution. Monthly you need to update the view from an external
event (maybe cron), to create the missing events for N years + one month...

 Thank you for your answer Craig,

  Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

pgsql-general by date:

Previous
From: Condor
Date:
Subject: Re: How to log query's from servers?
Next
From: Shad Keene
Date:
Subject: perl and php connect problems to pgsql all of a sudden