330580 - [PostgreSQL] Email may or may not be sent from BugMail::ProcessOneBug()

Status: RESOLVED WORKSFORME

(Bugzilla :: Database, defect)

Description

[Christian Höltje]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.1) Gecko/20060124 Firefox/1.5.0.1
Build Identifier: 2.20.1

Symptoms:
  When adding comments to the same bug without changing anything else, the list of people having email sent to them switches randomly: 
   "Email Sent To:" and "Excluding:" lists are swapped

Reason:
  At the top of BugMail::ProcessOneBug() there is a SQL statement (line150):
    SendSQL("SELECT " . join(',', @::log_columns) . ", lastdiffed, now() " .
            "FROM bugs WHERE bug_id = $id");

  The problem is the "now()".  It returns a string like "2006-03-15 10:42:27.247755-05" (a string version of timpstamptz) and is stored in $end.

  Later in the code,  (line 233) it compares "bug_when <= '$end'".  the problem is that bug_when is a timestamp(0) and would be a string like "2006-03-15 10:42:27".  This is because the seconds are rounded.

  When this comparison happens, it has a 50% chance of failure depending on whether the process_bug.cgi page is submitted at the top or bottom of a second.

Fixes:
  A cheap workaround that I'll be using is to coerce now() into a timestamp(0) --
  line 150 changes from:
SendSQL("SELECT " . join(',', @::log_columns) . ", lastdiffed, now() " .
to:
SendSQL("SELECT " . join(',', @::log_columns) . ", lastdiffed, now()::timestamp(0) " .

This isn't very portable.  Another solution would be to use the timestamptz type in the bug_when column.

Related:
  bug 283076 comment 15 seems to when it was decided to change the column to timestamp(0)

Reproducible: Sometimes

Steps to Reproduce:
1. Add a comment to bug
2. Notice who get's notified or not
3. Repeat -- notice that the who get's notified flips between emailed or exclude randomly.

Actual Results:  
see details

Expected Results:  
see details

Postgresq 7.4

Comment 1

[Christian Höltje]

My bad, it was bug 283076 comment 14 not 15 that is related.
I added bug 283076 comment 20 to point to this bug.

Comment 2

[Max Kanat-Alexander]

We changed it to LOCALTIMESTAMP(0) in 2.22. It's only fixed in 2.22, because 2.20's version of MySQL wouldn't support LOCALTIMESTAMP(0). It's probably the only major Pg bug left in the 2.20 series.

Comment 3

[Christian Höltje]

You changed all now() calls to LOCALTIMESTAMP(0)?  or the column?

Changing the column won't fix the problem.

Ciao!

Comment 4

[Max Kanat-Alexander]

(In reply to comment #3)
> You changed all now() calls to LOCALTIMESTAMP(0)?  or the column?

  Don't worry, we did the right thing. :-) 2.22 is fixed. 2.20 is not, and won't be.