Обсуждение: Recurring events
Hi, how do you store recurring events in a database? Selecting all events in a week/month should be fast (comming from an index). My solution looks like this: Table event: Columns: id, name, recurring, start_datetime, end_datetime recurring is weekly, monthly, yearly or NULL. end_datetime can be NULL (open end). Can you create an indexed view with infinite rows? I only want to index the last three year and the next three years. An other solution would be to fill a table with "serialized" events. The recurring events would be created and inserted into a table. This can only be done in a time frame like above (last three year, next three years). If a recurring event gets altered, all its serialized events need to be updated. Any feedback? Thomas Güttler -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de
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
Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit : > how do you store recurring events in a database? > > Selecting all events in a week/month should be fast (comming from an index). > > My solution looks like this: > > Table event: > > Columns: id, name, recurring, start_datetime, end_datetime > > recurring is weekly, monthly, yearly or NULL. > Maybe you could try something like what is used in cron, the scheduling program for GNU/Linux The crontab file looks like this # m h dom mon dow user command 17 * * * * root run-parts --report /etc/cron.hourly 25 6 * * * root run-parts --report /etc/cron.daily ) 47 6 * * 7 root run-parts --report /etc/cron.weekly ) 52 6 1 * * root run-parts --report /etc/cron.monthly ) m is minutes, h hours, dom day of month, mon month, dow day of week. line 1 executes all scripts in /etc/cron.hourly at 17 minutes after each hour; line 4 launches all scripts in /etc/cron.weekly every sunday at 06H47 using a similar structure for your table, you could then query for events occuring weekly, monthly (by finding a value in the proper column). You would have to add fields such as first_event_date, last_event_date, duration... > end_datetime can be NULL (open end). > Then the event is not recurring, it just keeps going; I would consider that a special case. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique
On 07.06.2011 09:57, Vincent Veyron wrote: > Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit : > >> how do you store recurring events in a database? >> >> Selecting all events in a week/month should be fast (comming from an index). >> >> My solution looks like this: >> >> Table event: >> >> Columns: id, name, recurring, start_datetime, end_datetime >> >> recurring is weekly, monthly, yearly or NULL. >> > > Maybe you could try something like what is used in cron, the scheduling > program for GNU/Linux I know cron very well. But I need to get all events on day X between time1 and time2 very quickly. If I build a crontab like table, I need to check all entries before I can know which crontab lines get executed during this period. Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de
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
Le mardi 07 juin 2011 à 13:28 +0200, Thomas Guettler a écrit :
>
> On 07.06.2011 09:57, Vincent Veyron wrote:
> > Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit :
> >
> >> how do you store recurring events in a database?
> >>
> >> Selecting all events in a week/month should be fast (comming from an index).
> >>
> >> My solution looks like this:
> >>
> >> Table event:
> >>
> >> Columns: id, name, recurring, start_datetime, end_datetime
> >>
> >> recurring is weekly, monthly, yearly or NULL.
> >>
> >
> > Maybe you could try something like what is used in cron, the scheduling
> > program for GNU/Linux
>
> I know cron very well. But I need to get all events on day X between time1 and time2 very quickly.
> If I build a crontab like table, I need to check all entries before I can
> know which crontab lines get executed during this period.
>
Well, this would require some thoughts and a study of the data, but the
idea is to do :
CREATE TABLE event (
id serial primary key,
name text,
dow integer,
dom integer,
h_start time);
insert into event (name, dow, h_start)
values ('event1', 1, '09:45');
insert into event (name, dow, h_start)
values ('event2', 2, '09:45');
select * from event where dow=(
SELECT EXTRACT(DOW FROM current_date));
id | name | dow | dom | h_start
----+--------+-----+-----+----------
2 | event2 | 2 | | 09:45:00
You would have to build the proper indexes. There are many possible
variations for the model, depending on what your data is like, how it is
generated, etc...
Another possibility, which might or might not apply, is to use a
structure inspired from a subscription database :
CREATE TABLE event_2 (
id serial primary key,
name text,
last_event_date timestamp,
delay_before_next_occurence interval);
insert into event_2 (name, last_event_date,
delay_before_next_occurence)
values ('event1', current_date - interval '1 day', '1 day');
insert into event_2 (name, last_event_date,
delay_before_next_occurence)
values ('event1', current_date - interval '2 days', '1 day');
select * from event_2 where last_event_date +
delay_before_next_occurence=current_date;
id | name | last_event_date | delay_before_next_occurence
----+--------+---------------------+-----------------------------
1 | event1 | 2011-06-06 00:00:00 | 1 day
A lot depends on the data and its distribution, which requires quite a
bit of study. I find it pays off rather well in application development
time afterwards, though.
--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique
On 6/06/2011 8:59, Thomas Guettler wrote: > Hi, > > how do you store recurring events in a database? > > Selecting all events in a week/month should be fast (comming from an index). > > My solution looks like this: > > Table event: > > Columns: id, name, recurring, start_datetime, end_datetime > > recurring is weekly, monthly, yearly or NULL. > > end_datetime can be NULL (open end). > > Can you create an indexed view with infinite rows? I only want to index > the last three year and the next three years. > > An other solution would be to fill a table with "serialized" events. The recurring > events would be created and inserted into a table. This can only be done in a time frame > like above (last three year, next three years). If a recurring event gets altered, > all its serialized events need to be updated. > > Any feedback? I have had success using the instructions at https://github.com/bakineggs/recurring_events_for -- Troy Rasiah
On Mon, Jun 06, 2011 at 12:59:44PM +0200, Thomas Guettler wrote: > how do you store recurring events in a database? Check this: http://www.justatheory.com/computers/databases/postgresql/recurring_events.html Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/