Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Interval aggregate regression failure (expected

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Michael Glaesemann <grzm(at)seespotcode(dot)net>, Michael Glaesemann <grzm(at)myrealbox(dot)com>, Michael Paesold <mpaesold(at)gmx(dot)at>, PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] Interval aggregate regression failure (expected
Date: 2006-08-26 02:40:21
Message-ID: 200608260240.k7Q2eL124053@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
I used your ideas to make a patch to fix your example:

	test=> select '41 months'::interval  / 10;
	   ?column?
	---------------
	 4 mons 3 days
	(1 row)

and

	test=> select '41 months'::interval  * 0.3;
	   ?column?
	---------------
	 1 year 9 days
	(1 row)

The trick was not to play with the division, but to check if the number
of seconds cascaded into full days and/or months.

---------------------------------------------------------------------------

Tom Lane wrote:
> Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> > ... I think this just confirms that there is some kind of rounding (or  
> > lack of) in interval_div. Kind of frustrating that it's not visible  
> > in the result.
> 
> I think the fundamental problem is that the float8 results of division
> are inaccurate, and yet we're assuming that we can (for instance) coerce
> them to integer and get exactly the right answer.  For instance, in the
> '41 months'/10 example, I get month_remainder_days being computed as
> 
> (gdb) p month_remainder
> $19 = 0.099999999999999645
> (gdb) s
> 2575            result->day += (int32) month_remainder_days;
> (gdb) p month_remainder_days
> $20 = 2.9999999999999893
> 
> The only way we can really fix this is to be willing to round off
> the numbers, and I think the only principled way to do that is to
> settle on a specific target accuracy, probably 1 microsecond.
> Then the thing to do would be to scale up all the intermediate
> float results to microseconds and apply rint().  Something like
> (untested)
> 
> 	month_remainder = rint(span->month * USECS_PER_MONTH / factor);
> 	day_remainder = rint(span->day * USECS_PER_DAY / factor);
> 	result->month = (int32) (month_remainder / USECS_PER_MONTH);
> 	result->day = (int32) (day_remainder / USECS_PER_DAY);
> 	month_remainder -= result->month * USECS_PER_MONTH;
> 	day_remainder -= result->day * USECS_PER_DAY;
> 
> 	/*
> 	 * Handle any fractional parts the same way as in interval_mul.
> 	 */
> 
> 	/* fractional months full days into days */
> 	month_remainder_days = month_remainder * DAYS_PER_MONTH;
> 	extra_days = (int32) (month_remainder_days / USECS_PER_DAY);
> 	result->day += extra_days;
> 	/* fractional months partial days into time */
> 	day_remainder += month_remainder_days - extra_days * USECS_PER_DAY;
> 
> #ifdef HAVE_INT64_TIMESTAMP
> 	result->time = rint(span->time / factor + day_remainder);
> #else
> 	result->time = rint(span->time * 1.0e6 / factor + day_remainder) / 1.0e6;
> #endif
> 
> This might need a few more rint() calls --- I'm assuming that float ops
> with exact integral inputs will be OK, which is an assumption used
> pretty widely in the datetime code, but ...
> 
> Per the comment, if we do this here we probably want to make
> interval_mul work similarly.
> 
> 			regards, tom lane

-- 
  Bruce Momjian   bruce(at)momjian(dot)us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Attachment: /pgpatches/interval
Description: text/x-diff (1.6 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-08-26 03:23:35
Subject: Re: Further reduction of bufmgr lock contention
Previous:From: Jim C. NasbyDate: 2006-08-26 01:22:49
Subject: Re: integration of pgcluster into postgresql

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-08-26 03:29:47
Subject: Re: Adding fulldisjunctions to the contrib
Previous:From: Bruce MomjianDate: 2006-08-26 00:08:48
Subject: Re: plpython improvements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group