Re: [BUGS] BUG #2477: Aggregate Integer divisors incorrectly yield integer-type quotient

#1

"John Rylander" <rylander (AT) prolexia (DOT) com> writes:

Quote:

When for some reason I did a sum/count instead of an avg, the result was
always an integer (either 0 or 1):

What's your point? Postgres has always defined division of integers as
yielding an integer result. AFAICS this choice is fully sanctioned by
the SQL standard:

1) If the data type of both operands of a dyadic arithmetic opera-
tor is exact numeric, then the data type of the result is exact
numeric, with precision and scale determined as follows:
...
d) The precision and scale of the result of division is
implementation-defined.

(SQL92 6.12 <numeric value expression> syntax rule 1)

Quote:

Yet another maddening PostgreSQL bug/idiosyncrasy. Time for this boy to go
to Oracle

Sayonara. Last I checked, they had a few idiosyncrasies too.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#2

On Sun, Jun 11, 2006 at 09:08:00PM +0000, John Rylander wrote:

Quote:

The following bug has been logged online:

Bug reference: 2477
Logged by: John Rylander
Email address: rylander (AT) prolexia (DOT) com
PostgreSQL version: 8.1.4
Operating system: Windows XP

Description: Aggregate Integer divisors incorrectly yield
integer-type quotient
Details:

When for some reason I did a sum/count instead of an avg, the result was
always an integer (either 0 or 1):

Sum(
Case when Sign(Delta)=Sign(AvgDelta) then
1
else
0
) / Count(*) as HitRate;

If I use "/ Count(*)::float4", or use Avg instead of Sum/Count, it works
fine.

Do you have a test case for this?

Quote:

Yet another maddening PostgreSQL bug/idiosyncrasy. Time for this boy to go
to Oracle (not because of this, but because of the function->transaction
problem, such that a function that has nested loops that create myriad
temporary analytical tables necessarily runs out of disk space; if it
weren't for the mandatory and completely counterproductive implicit
transaction, it'd take very little space and a lot less time).

Huh? What implicit transaction? Do you have an example that illustrates
this problem?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#3

"John E. Rylander" <rylander (AT) prolexia (DOT) com> writes:

Quote:

... I have a PL/PGSQL analytical
function that iteratively drops a table, queries to create a new table of
the same name based on iterated parameters, and then runs numerous queries
on the resulting table, storing a few summary records to a log table. The
nested loops in the function intelligently iterate tens or hundreds of
thousands of times on tables containing tens or low-hundreds of thousands of
records.
The problem is, I think, since this function is involuntarily
wrapped in a transaction, PostgreSQL wastes a TON of time and space keeping
track of every single interim table, and eventually (pretty soon, in fact)
it runs out of disk space.

It might be possible to teach the system that it could immediately
unlink the table file when dropping a table that was created in the
current transaction. See the NOTE in smgrscheduleunlink() for a hint
about one way to do it. I think you'd have to worry about matching
subtransaction nest levels, but if they're the same then allowing
inverse unlink requests to "blow each other up" would work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2477: Aggregate Integer divisors incorrectly yield integer-type quotient

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2477: Aggregate Integer divisors incorrectly yield integer-type quotient - 06-11-2006 , 10:04 PM

Re: [BUGS] BUG #2477: Aggregate Integer divisors incorrectly yield integer-type quotient - 06-12-2006 , 10:49 AM

Re: [BUGS] BUG #2477: Aggregate Integer divisors incorrectly yield integer-type quotient - 06-13-2006 , 09:56 AM