Error in 5.x-1.x and 5.x-2.x with postgresql

I'm guessing this is a postgresql issue. Does anyone with a bit more knowledge about postregsql have any ideas? Unfortunately, I don't have a server with that set up to test on.

Here is some more information which may be helpful:

I created a single repeating event set for June 3, 2007, to repeat weekly until July 1, 2007. I would assume this would create an event each Sunday. I didn't set any advanced options.

The event repeat debug log looks like the following:

10001 Jun 3 2007 - 11:59pm Jul 1 2007 - 11:59pm Jun 3 2007 - 11:59pm Mon, 06/04/2007 - 7:59pm Sunday School 1

I then run cron.php, and get tons of errors similar to the following:

Message query: INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2012-11-16', 1353110399,'FR','3FR','-3FR','16','-15','11','321','-46','46','-7') in /var/www/html/raleigh/includes/database.pgsql.inc on line 113.
Severity error

If I manually type this into postgres I see the following:

raleigh=# INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2012-11-16', 1353110399,'FR','3FR','-3FR','16','-15','11','321','-46','46','-7');
ERROR: duplicate key violates unique constraint "event_repeat_calendar_map_pkey"

So, I check and see the following when I query based on the date_stamp:

raleigh=# SELECT * from event_repeat_calendar_map where date_stamp='1353110399';
day_stamp | date_stamp | day_of_week | day_in_month | day_in_month_r | month_day | month_day_r | month | year_day | year_day_r | week_number | week_number_r
------------+------------+-------------+--------------+----------------+-----------+-------------+-------+----------+------------+-------------+---------------
2012-11-16 | 1353110399 | FR | 3FR | -3FR | 16 | -15 | 11 | 321 | -46 | 46 | -7
(1 row)

I hope this is helpful. I can give you any other information you need.

--Drew

OK. Perhaps some explaining to me will help me figure out this problem.

1) event_repeat_calendar_map just contains a list of all the days for the next 2000 days and creates some mapping for figuring out when repeat events should occur, kind of like a lookup table, right? If so, it appears that the first time I run cron.php it populates this table, but on subsequent runs whatever checking is done to make sure the table isn't already populated is failing, and I get a failed INSERT for each of the 2000 days that are already in the table. I tried to figure out how the logic is done for this, but couldn't quite figure it out.

2) when a repeating event is created, it is put into event_repeat, and also the first instance is put in the event table. When cron runs I assume it creates new nodes as well as new events in the event table for each actual event. So, if I set the repeat count to 4, after the cron run I then have 4 nodes for that event and 4 entries in the event table pointing to the nodes, right? After my cron run there isn't a new event or node created anywhere.

I am trying to figure out where in the eventrepeat module this occurs and am having no luck.

Please give me some guidance here and perhaps I can help track down the issue.

Thanks!

Your guess that the check to see if the table was already populated sounds right. The date field is a UNIQUE_INDEX, so it makes sense that this is where it is failing.

For the second point, new nodes are actually created for each instance. (That is the main focus of http://drupal.org/node/87600). A number of nodes are created up until the initial render period setting. From there, each time cron is run, it adds any new days to the calendar_map table out on the horizon of the render support period. After these new entries are in, the call to _event_repeat_render_nodes() creates the new nodes.

All of this is happening in eventrepeat_cron, so that is where we should focus our attention. If you start at the top, and begin adding watchdog messages right before each db_query() line so that we can see all of the sql statements that are being executed, that might get us some more info.

Please give me an example of what you want me to put before each db_query and I'll do it.

Thanks!

Something like...

...where $sql is whatever is in the db_query should do the trick. See http://api.drupal.org/api/5/function/watchdog for more info. The main thing we want to figure out is if there is a query in there that is not correctly finding that there is already an entry for that day.

As a side note, here is something else to watch for. Cron is not the only way that event nodes are created. Browsing into the future will also create nodes where they are needed. Consider setting the render nodes down a bit also, just to keep it from giving you too much info. I'll try and take a shot at this as well, just to make sure it is not something that is db specific.

Is this what you want? I set the render days to 3.

Thanks!

* warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key violates unique constraint "event_repeat_calendar_map_pkey" in /var/www/html/raleigh/includes/database.pgsql.inc on line 94.
* user warning: query: INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2007-05-30', 1180569599,'WE','5WE','-1WE','30','-2','5','150','-216','22','-31') in /var/www/html/raleigh/includes/database.pgsql.inc on line 113.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key violates unique constraint "event_repeat_calendar_map_pkey" in /var/www/html/raleigh/includes/database.pgsql.inc on line 94.
* user warning: query: INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2007-05-31', 1180655999,'TH','5TH','-1TH','31','-1','5','151','-215','22','-31') in /var/www/html/raleigh/includes/database.pgsql.inc on line 113.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key violates unique constraint "event_repeat_calendar_map_pkey" in /var/www/html/raleigh/includes/database.pgsql.inc on line 94.
* user warning: query: INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2007-06-01', 1180742399,'FR','1FR','-5FR','1','-30','6','152','-214','22','-31') in /var/www/html/raleigh/includes/database.pgsql.inc on line 113.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key violates unique constraint "event_repeat_calendar_map_pkey" in /var/www/html/raleigh/includes/database.pgsql.inc on line 94.
* user warning: query: INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2007-06-02', 1180828799,'SA','1SA','-5SA','2','-29','6','153','-213','22','-31') in /var/www/html/raleigh/includes/database.pgsql.inc on line 113.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key violates unique constraint "event_repeat_calendar_map_pkey" in /var/www/html/raleigh/includes/database.pgsql.inc on line 94.
* user warning: query: INSERT INTO event_repeat_calendar_map (day_stamp, date_stamp, day_of_week, day_in_month, day_in_month_R, month_day ,month_day_R, month, year_day, year_day_R, week_number, week_number_R) VALUES('2007-06-03', 1180915199,'SU','1SU','-4SU','3','-28','6','154','-212','22','-31') in /var/www/html/raleigh/includes/database.pgsql.inc on line 113.

Any updates on this?

Thanks!

I think this is a problem in 5.2 aswell

I'm pretty sure this is probably an issue with the schema for pgsql tables. I'm not really an expert at pgsql, so I cant' comment on this. At a guess, I'd say it might have something to do with the way the indexes were built on the event_repeat_calendar_map table. These have been in since day one of the .install files, so I don't have any history here.

If someone more familiar with pgsql indexes could take a look at the .install file, that would be helpful.

I can confirm this for Drupal 5.2 to. I get the same errors when running the cron job.

I have had a quick look at the code...

The following seems to fix the problem for me... any help?

Rgs
Jason

--- eventrepeat.module.orig 2007-06-06 23:55:30.000000000 +0100
+++ eventrepeat.module 2008-01-17 13:21:33.000000000 +0000
@@ -57,10 +57,11 @@
//current time is calculated in GMT, then we check the table for the last created date. if no records are in the
//table, set start date to the end of the day on the day before the current date, and enable the seed.
$curtime = time();
- $result = db_query("SELECT MAX(date_stamp) FROM {event_repeat_calendar_map}");
+ $result = db_query("SELECT MAX(date_stamp) as date_stamp FROM {event_repeat_calendar_map}");
$start = db_fetch_array($result);
- if ($start['MAX(date_stamp)']) {
- $start = $start['MAX(date_stamp)'];
+
+ if ($start['date_stamp']) {
+ $start = $start['date_stamp'];
} else {
$start = gmmktime(23, 59, 59, (int) gmdate('n', $curtime), (int) gmdate('j', $curtime), (int) gmdate('Y', $curtime)) - 86400;
$seed = TRUE;

If someone can confirm that this fix works, I'll get it in. Also, can you repost that as an actual attached patch if you get a chance?

Changes in eventrepeat.module are OK for my Postgresql.

Thanks a lot.

this patch solved the problem for me w/

drupal- 5.16
eventrepeat-5.x-1.0
postgresql-server-8.3.7